Forum Discussion

chavanr's avatar
chavanr
Resolver I
4 years ago
Solved

Calculate Distinct values

Hi there,   Just wondering if someone can help with the below:   I want to calculate distinct value for my measure   Serious Incident Total = CALCULATE( [Total Incidents] - [Total Not Ser...
Need Help DAX measure
  • rbriga's avatar
    rbriga
    4 years ago

    This should get you the result you wanted- worked for my test:

    Serious Incident Total =
VAR _Incidents = CALCULATETABLE(
    DISTINCT( Forms[itrak_formid] ),
    Forms[Form Type] = "Incident & Investigation"
) 
VAR _NotSerious = CALCULATETABLE(
    DISTINCT( Forms[itrak_formid] ),
    Forms[Category] = "Not Serious"
) 

RETURN
COUNTROWS(
    EXCEPT( _Incidents, _NotSerious )
)

    We create a table of distinct incidents (_Incidents), a table of distinct not serious forms (_NotSerious),

    Remove IDs that appear in _NotSerious from _Incidents,

    And count the rows on the resulting table.

rbriga's avatar
rbriga
Impactful Individual
4 years ago

This should get you the result you wanted- worked for my test:

Serious Incident Total =
VAR _Incidents = CALCULATETABLE(
    DISTINCT( Forms[itrak_formid] ),
    Forms[Form Type] = "Incident & Investigation"
) 
VAR _NotSerious = CALCULATETABLE(
    DISTINCT( Forms[itrak_formid] ),
    Forms[Category] = "Not Serious"
) 

RETURN
COUNTROWS(
    EXCEPT( _Incidents, _NotSerious )
)

We create a table of distinct incidents (_Incidents), a table of distinct not serious forms (_NotSerious),

Remove IDs that appear in _NotSerious from _Incidents,

And count the rows on the resulting table.