Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

SUM FREQUENCY from Data Table to Report Table (DAX Required)

 

Hi,

 

I would like to get the unique count based on the data table (item and status) according to the comments in report table.

 

In Excel I am applying the following formula F2=SUM((FREQUENCY(MATCH(A$2:$A$19&"",$A$1:$A$19&"",0)*($B$2:$B$19=$D3),ROW($A$2:$A$19))>0)+0)-1 in order to get the my final result.

 

In data table, The item against updated the status in data table. The same item can not be two different comments. The item are repeated as well..  

 

DATA:

 

ITEMSTATUS
234MATCHED
234MATCHED
234MATCHED
234MATCHED
234MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
235NOT MATCHED
1234MATCHED
7890NOT MATCHED
67890NOT MATCHED

 

REPORT TABLE:

 

COMMENTSDESIRED RESULT
MATCHED2
NOT MATCHED3

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Saxon10 ,

     

    Check the formula.

    Column = CALCULATE(DISTINCTCOUNT('Table'[ITEM]),FILTER('Table','Table (2)'[comments]='Table'[STATUS]))

     

    Best Regards,

    Jay

12 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 add following measure and in the visual, use Status and this measure.

     

    Match Count = DISTINCTCOUNT ( Match[ITEM] )

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi,

       

      Your soltion not working. Can you please share your output. 

      I want DAX solutions. I don't want measue option. 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 I have no idea what are you saying, you need DAX solution, not a measure. A measure is done using DAX expression. You need to clearly tell what you are looking for.

     

    Here is the output 

     

     

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      thanks for your quick reply. Sorry for the inconvenience. I mean by calculate column.

       

      I want calculate column in my report table based on the data table {Item and status}.

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 try this as a column

     

    Match Count Column = 
CALCULATE ( DISTINCTCOUNT ( Match[ITEM] ), ALLEXCEPT ( Match, Match[STATUS] ) )

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Thanks for your reply again. Now I got the unique count in my data table according to the item and status. I want to know no of matched and not matched in my report table.

      I want new calculate column in my report table.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 you already have a count column why you need another one. Sorry to say but your requirement is all over the place. 

     

    Just use table visual, use status and count column in the visual, and make sure don't aggregate count column otherwise it will show the sum.

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    I still don't understand the purpose of this adding as a column.

     

     

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Sorry for the late response.

       

      The reason I want calculated column in report table for multiple legends in one chart. I already build lot column in report table so I would like to add one more column which is unique count according to the comments.

       

      can you please advise.

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

    Hi,

    To your Table, drag the status column and write this measure

    Measure = distinctcount(data[item])

    Hope this helps.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 super confusing, you already have a column, why you need another column. I'm totally lost. Sorry, maybe I'm not getting what you are trying to do here. If already gave you a solution with a column, why you need another column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Saxon10 ,

     

    Check the formula.

    Column = CALCULATE(DISTINCTCOUNT('Table'[ITEM]),FILTER('Table','Table (2)'[comments]='Table'[STATUS]))

     

    Best Regards,

    Jay

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      thank you so much for your help. This is I am looking for it. Its very simple and awesome.