topic Re: Calculate Distinct values in DAX Commands and Tips

Calculate Distinct values

chavanr — Fri, 04 Mar 2022 01:42:46 GMT

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 Serious Incidents],

FILTER(Forms,NOT(ISBLANK(Forms[Category])))

)

Total Not Serious Incidents =

CALCULATE(

DISTINCTCOUNT(Forms[itrak_formid]),

FILTER(Forms,Forms[Category]= "Not Serious")

)

Total Incidents =

CALCULATE(

DISTINCTCOUNT(Forms[itrak_formid]),

FILTER('Forms',Forms[Form Type] = "Incident & Investigation")

)

Re: Calculate Distinct values

amitchandak — Fri, 04 Mar 2022 03:16:55 GMT

chavanr , if want distinct values a measure first you need to get a grouping for that

measure =

var _tab = summarize(Forms,Forms[Column], "_1",[Total Incidents ])

return

countx(values(_tab[_1],[_1])

measure =

var _tab = summarize(Forms,Forms[Column], "_1",[Total Incidents ])

return

countx(summarize(_tab, _tab[_1],[_1])

Re: Calculate Distinct values

v-angzheng-msft — Tue, 08 Mar 2022 05:05:36 GMT

Hi, chavanr

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented? And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

Sample (dummy dataset) data as text, use the table tool in the editing bar
Expected output from sample data
Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate Distinct values

rbriga — Tue, 08 Mar 2022 06:59:57 GMT

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.