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 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?

 

 

 

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.

     

5 Replies

  • 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.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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] ) )