Forum Discussion

Nath_nath's avatar
Nath_nath
Frequent Visitor
4 years ago
Solved

Creating a distinct count measure based on a total

Dear fellow PBI enthusiasts, I am having more than expected trouble finishing this measure. I would like to calculate the unique number of cases where total case amount (irregards used slicers) is...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Nath_nath,

     

    Try these measures. The variable vCaseTotals is a virtual table that contains the total minutes for each case, which is then filtered for "> 100". Then, the virtual table of cases > 100 is used as a CALCULATE filter.

     

    Total Minutes = SUM ('fact'[Minutes] )
    Distinct Count Minutes > 100 = 
VAR vCaseTotals =
    ADDCOLUMNS (
        VALUES ( 'fact'[Case] ),
        "@Minutes", CALCULATE ( [Total Minutes], ALLEXCEPT ( 'fact', 'fact'[Case] ) )
    )
VAR vCasesOver100 =
    FILTER ( vCaseTotals, [@Minutes] > 100 )
VAR vResult =
    CALCULATE ( DISTINCTCOUNT ( 'fact'[Case] ), vCasesOver100 )
RETURN
    vResult

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

Nath_nath,

 

Try these measures. The variable vCaseTotals is a virtual table that contains the total minutes for each case, which is then filtered for "> 100". Then, the virtual table of cases > 100 is used as a CALCULATE filter.

 

Total Minutes = SUM ('fact'[Minutes] )
Distinct Count Minutes > 100 = 
VAR vCaseTotals =
    ADDCOLUMNS (
        VALUES ( 'fact'[Case] ),
        "@Minutes", CALCULATE ( [Total Minutes], ALLEXCEPT ( 'fact', 'fact'[Case] ) )
    )
VAR vCasesOver100 =
    FILTER ( vCaseTotals, [@Minutes] > 100 )
VAR vResult =
    CALCULATE ( DISTINCTCOUNT ( 'fact'[Case] ), vCasesOver100 )
RETURN
    vResult