Forum Discussion

obriaincian's avatar
obriaincian
Icon for Resolver I rankResolver I
3 years ago
Solved

Dax Calculated column with calculation

Hi All,

 

My dataset looks like this

 

Company           ID                     Status                       Submitted        
Apple11TD Complete 
Apple11TD CompleteSubmitted
Microsoft22TD Complete 
Google333TD Complete 
Google333TD Complete 
Google333TD Complete 
Amazon44TD Complete 
Amazon44TD Complete 
Amazon44TD Complete 
Amazon55TD Complete 
Amazon66TD CompleteSubmitted

 

 

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  
Apple10
Microsoft11
Google11
Amazon32
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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"
            )
)