Forum Discussion

diablo9083's avatar
diablo9083
Frequent Visitor
1 year ago
Solved

DISTINCTCOUNT Not working as expected in Matrix Visual

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 o...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
SamWiseOwl
Super User
1 year ago

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's avatar
diablo9083
Frequent Visitor
1 year ago

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

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.