Forum Discussion

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
3 years ago
Solved

Count Occurrence in a Column- must be dynamic

I have a column of data with recurring entries.  There are about 10 unique choices, but these can occur an infinite of times.     I would like to count how many times each reason appears, but...
Need Help
  • bharath_v's avatar
    bharath_v
    3 years ago

    Hi aflintdepm 

    I was able to simulate the output without using any DAX.

     

    In matrix visual, do these steps:

    Rows -> Date

    Columns -> Reason

    Values -> Count of Reason (not distinct)

               -> Count of Reason (Show value as percentage of total)

     

    Rename the value headers accordingly.

    Output:

    Let me know if I am missing something. 

     

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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

Create a Calendar Table with a relationship (Many to One and SIngle) from the Date column of the Data Table to the Date column of the Calendar Table.  To the matrix visual, drag Date from the Calendar Table to row labels and Reason to Column labels.  Write this measure and drag it to the visual

Count = countrows(Data)

Count (%) by date = calculate([Count],all(calendar))

Count (%) by Reason = calculate([Count],all(Data[Reason]))

Format the last 2 measures as %.

Hope this helps.

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
3 years ago

Ashish_Mathur 

This got me closer, but is not working properly

  1. I had a calendar table identified already, so that part worked.  However, when I add it to the matrix, it retruns dates that are not in the fact table.  My data is only from 12/29/22, but the matrix is returning values from 6/1/20
  2. Only the Count % by Date is returning results.  The % Count by Reason is blank in all columns
  3. The Count % by Date is returning multiple results >100%, so something is wrong there

I know it is difficult to assist without sample data to manipulate, so I truly appreciate your help.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    I will need to see your file.