Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

distinct count question

Hello Community, 

 

I have a situation where I need to count the distinct count of "problem codes" against their corresponding order.  

 

The table below perfectly illustrates the issue I am trying to solve.   

 

The count of RMAs is a distinct count.   I need the Header Problem code count to match that  (72).   So I need a measure that equates the RMA to the header problem code, and sums up the correct number of header problem codes  (which should match the distinct count of RMAs).     

 

In the table below, you can see if I just use the options  -  count or count distinct  -  I end up with the wrong values (for what I need) for the problem header codes.   The "distinct count" option just gives me the distinct count of the header code types in this data set  (3 in this case).  T3  T1  T5.      The "count" option just gives me the count at the line level.   

 

I need a "distinct count" of header codes, but I need it to be tied to the distinct count of the RMAs since each RMA will have a corresponding heading problem code.

 

Any guidance is appreciated!

 

 

  • Hi, 

    Please correct me if I understood wrongly, but I think you can try the below.

     

    Count of Header Problem Code =
    If ( isfiltered(RMA),

         distinctcount(Header Problem Code),

         [Count of RMA] )

     

    I am not sure if I mentioned column names correctly.

    If it is possible to share your pbix file, it will be much helpful for me to look into it more deeply.

     

    Thanks.

3 Replies

  • Hi, 

    Please correct me if I understood wrongly, but I think you can try the below.

     

    Count of Header Problem Code =
    If ( isfiltered(RMA),

         distinctcount(Header Problem Code),

         [Count of RMA] )

     

    I am not sure if I mentioned column names correctly.

    If it is possible to share your pbix file, it will be much helpful for me to look into it more deeply.

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable

      That worked perfectly.   Here is the final code and picture of the solution. 

      Thanks!

       

      Distinct Count of Header Problem Code =
      IF( ( ISFILTERED(Merge1[RMA]),
      DISTINCTCOUNT(Merge1[Header Problem Code]),
      [Distinct Count of RMAs] )

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User

        That's great. I am glad that I could help.

        I hope you have a great weekend. ğŸ˜€