POWER BI - DAX - Group By [ SUMMARIZE() ]

  Sometime we wants to use some aggregate function like MIN, MAX, COUNT, etc.. but that should be grouped by some dimension fields. In Power BI we just cant create a new column we MIN or MAX function. that will give us MIN and MAX value from the entire data set. So in order to achieve our requirement we  need to use SUMMARIZE ().

Create a new Table use the below formula .

Groupedby_Table = SUMMARIZE (Dim1,Dim2,"Column Name 1 - Min", MIN(X),"Column Name 2 - Max", MAX(X))

Follow below example :

Employee_INOUT = SUMMARIZE(

'Access JournalLogView',

'Access Personnel'[Emp No],'Access Personnel'[Employee Name],'Access JournalLogView'[Entry/Exit Date].[Date],'Access Personnel'[Ref. Ind],

"Entry Time" , MIN('Access JournalLogView'[Entry/Exit Time]),

"Exit Time" , MAX('Access JournalLogView'[Entry/Exit Time]))

POWER BI - DAX - New Column for Flag

 when you wants to create a new column which will be used as flag.

Create a new column and use the below formula 

Flag = IF ( X1=X2 , "Yes","No")

Example ..

FLAG = IF(WC_ASSAI_TRANSMITTAL_F[LATEST_REV]=WC_ASSAI_TRANSMITTAL_F[REVISION_CODE], "Y","N")

POWER BI - DAX - New Measure ( Distinct Count with Multiple Filters )

 When you ants to create a new measure like count or distinct count with some multiple filters.

CALCULATE ( COUNT ( X , FILTER(Exp1, Exp2, ...)))

CALCULATE ( DISTINCTCOUNT ( X , FILTER(Exp1, Exp2, ...)))

Check the below example..

CALCULATE (DISTINCTCOUNT (WC_ASSAI_TRANSMITTAL_F[DOCS_SEQ_NR]),FILTER(WC_ASSAI_TRANSMITTAL_F,WC_ASSAI_TRANSMITTAL_F[FLAG]="Y" && WC_ASSAI_TRANSMITTAL_F[RETURN_DATE] = BLANK() && WC_ASSAI_TRANSMITTAL_F[RESPONSIBLE_ENGINEER_IND]="Y" && WC_ASSAI_TRANSMITTAL_F[DUE_DATE]< TODAY() ))

POWER BI - DAX - Add Offset with Date

Add or minus offset (DAY,HOUR,MIN,SEC ) with datetime then we can use below one.

Use new Column with below formula

[DateTime] + #duration(0,10,0,0)

##duration(DAY,HOUR,MIN,SEC) 

-------------------------------------------------------------

Add or minus offset (DAY,HOUR,MIN,SEC ) with datetime then we can use below one.

Use new Column with below function.

DATEDIF(date1,date2,interval)

Interval : DAY/MONTH/YEAR.

--------------------------------------------------------------

To get the current date use TODAY()  or NOW()

POWER BI - DAX - Create Calendar Table

 Use the below Code.

New Table -> 

Calender = 

ADDCOLUMNS (

    CALENDAR ("01-Jan-2013", "31-Dec-2030"),

    "DATE_WID", FORMAT([Date],"YYYYMMDD"),

    "Creation Date", FORMAT([Date],"DD-MMM-YYYY"),

    "Year", FORMAT([Date],"YYYY"),    

    "Month", FORMAT([Date],"M"),

    "MonthName",FORMAT([Date],"MMM"),

    "Quarter", "Q" & FORMAT ( [Date], "Q" ))