DISTINCTCOUNT Not working as expected in Matrix Visual

[diablo9083]

I have a matrix visual, and I need to use DISTINCTCOUNT in a calculation for the matrix, but I can't get it to aggregate. If I toss the column into the matrix and put the distinct count aggregation on it, it works fine (Leftmost Days of Charges below). If I make a column Days of Charges = DISTINCTCOUNT(Table[Date]), distinct count doesn't aggregate by department. It just gives me a scalar value. How do I get DISTINCTCOUNT(Table[Date]) to aggregate properly?

[danextian]

Hi @diablo9083 

Since the first Days of Charges works fine, I am  assuming you're trying to get the sum of this aggregation at the total level.  If so try,

SUMX ( VALUES ( 'table'[deparment code] ), DISTINCTCOUNT ( 'table'[Date] ) )

[SamWiseOwl]

Hi @diablo9083 
Calculated columns don't automatically get filtered.
If you create a measure and use DistinctCount(table[column]) then it will automatically filter.

Measures are dynamic and will change based on slicers and other visuals.

Should you absolutely require it to be a column use Calculate(DistinctCount(table[column])) but this will NOT be dynamic in your visual.

[diablo9083]

I gave this a shot, and it didn't seem to work either.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from danextian and SamWiseOwl , please allow me to provide another insight:

Hi, @diablo9083 

Regarding the issue you raised, my solution is as follows:

I share the same idea as SamWiseOwl. If your expected result is to directly use the distinct values of the date column, as shown in the image below:

Then I suggest you directly create a measure:

Days of Charges = DISTINCTCOUNT('Table'[Date])

Here is my sample data:

The final result is as follows:

If you wish to create a calculated column, I recommend trying the following calculated column:

Column = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[ChargeID],'Table'[Department]))

However, the total part will still need to be adjusted using a measure:

MEASURE =
IF (
    HASONEVALUE ( 'Table'[ChargeID] ),
    MAX ( 'Table'[Column] ),
    DISTINCTCOUNT ( 'Table'[Date] )
)

Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[SamWiseOwl]

Can you provide a sample of the data or mock up some data.