Forum Discussion
Solved
Dax Calculated column with calculation
Hi All,
My dataset looks like this
| Company | ID | Status | Submitted |
| Apple | 11 | TD Complete | |
| Apple | 11 | TD Complete | Submitted |
| Microsoft | 22 | TD Complete | |
| 333 | TD Complete | ||
| 333 | TD Complete | ||
| 333 | TD Complete | ||
| Amazon | 44 | TD Complete | |
| Amazon | 44 | TD Complete | |
| Amazon | 44 | TD Complete | |
| Amazon | 55 | TD Complete | |
| Amazon | 66 | TD Complete | Submitted |
I now want to create a calculated table,
I want this table to be grouped by company and show the count of DISTINCT IDS that are Completed.
The second part is to then have another column in the table that shows the DISTINCT count of IDS that are Completed MINUS DISTINCT count of IDS that are submitted.
RESULT
| Company | Complete | Complete -Submitted |
| Apple | 1 | 0 |
| Microsoft | 1 | 1 |
| 1 | 1 | |
| Amazon | 3 | 2 |
Hi,
Please check the below picture and the attached pbix file.
New Table = ADDCOLUMNS ( DISTINCT ( Data[Company] ), "Complete", CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" ), "Complete-Submitted", CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" ) - CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete", Data[Submitted] = "Submitted" ) )
1 Reply
Hi,
Please check the below picture and the attached pbix file.
New Table = ADDCOLUMNS ( DISTINCT ( Data[Company] ), "Complete", CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" ), "Complete-Submitted", CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete" ) - CALCULATE ( COUNTROWS ( DISTINCT ( Data[ID] ) ), Data[Status] = "TD Complete", Data[Submitted] = "Submitted" ) )