Distinct Count in a measure vs a in a column of a table

[Hussain]

Hi,

I have written DAX measures involving distinct count which work fine and as expected but when I turn them into a column they give the wrong answers. Allow me to explain.

I  have a DAX measures like so -

1.

SummaryConsumption = CALCULATE(DISTINCTCOUNT(SummaryTable[OwnerId]),DATESBETWEEN(DateTable[Dates].[Date],FIRSTDATE(DateTable[Dates].[Date]),LASTDATE(DateTable[Dates].[Date])))

2. 

AorP0 = IF(ISBLANK([SummaryConsumption]) = BLANK(),
IF([SummaryConsumption]=1,"Assigned","Pooled"))

The resultant ouput is like this (which is correct) - 

But I want to convert the above measures to columns so that I can later summarize/group that table on the column AorP0

The final output I want is a table listing how many devices are assigned or pooled. For example, in the above table we have Assigned =2 and Pooled =1.

But when I turn the measures into columns the distinct count is not giving the output as expected (I am not copying the column formula again as it is the exact same thing - just that I now  write the column in New Column in the original table).

Here is the output table (this is the wrong output) - 

For the device BWC2-007607, the correct count is 2 because 2 ownerids are same and 1 different.

@amitchandak, or anyone who might be able to help?!

[mattww · Accepted solution]

Hi @Hussain 

DISTINCTCOUNT isn't suitable for use as a Calculated Column.

Calculated columns work on a row level, meaning the value in that column can only be based on what's on the same row, a DISTINCTCOUNT is an aggregate function across multiple rows so it can only be a Measure

Also, calculated columns are calculated at refresh, and will not change when you interact with the report, whereas Measures are dynamic.

You could create a calculated summary table, using the SUMMARIZE function

See SUMMARIZE – DAX Guide

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