Distinct Filter not giving the result expected

[chiru5262]

Below is my issue when I do distinctcount on 'assetnum' the total is 4145, but when I seperate them by filter BM/CM the count total is 2020+2425  which is 4445 which doesnt match with 4145.

I did exported and checked the 4145 for some reason its excluding the worktype = 'BM','CM' filter not sure why.

@amitchandak @lbendlin could you please help, appreciate it

[mickey64]

I don't know your detailed data, so I'll explain with a simple data example.
In the table below, if you run "DISTINCTCOUNT" on just "BM", the result will be "4".
Also, if you run "DISTINCTCOUNT" on just "CM", the result will be "3", and the sum of these is "7" (=4+3).
But if you run "DISTINCTCOUNT" on "BM&CM", the result will be "6". This is because there is duplicate "Assetnum" data, as marked in yellow in the table.

[chiru5262]

Hi @mickey64 

That makes sense, but how can I acheive 7, as I need the output of 7 for my case.

appreciate your help

Thanks,

Chiru

[mickey64 · Accepted solution]

I think you can achieve your goal with the 'Visual Calculations'.

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

[chiru5262]

@mickey64  You are legend mate, thanks thats works

[lbendlin]

You need to materialize the individual calculations via SUMMARIZE or SUMMARIZECOLUMNS  if you want the DISTINCTCOUNT by category rather than across categories.

[chiru5262]

@lbendlin  thanks for your response, could you please send me the dax measure I can use for the above.

[chiru5262]

@mickey64  or any way to show that unique 1 assetnum which had both BM and CM against it