Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Changing aggregation condition based on condition

Hi all I’m new to PowerBI and struggling with a puzzle on "changing the aggregation function depending on a condition" (I think)… Google hasn’t helped me solve it. To reduce it to a “simple” example...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    Would you please try the following dax:

     

     

    Table 2 = SUMMARIZE('Table','Table'[ID],"Count",COUNT('Table'[ID]),"total",IF('Table'[ID] = "anon",SUM('Table'[Score]),MAX('Table'[Score])))

     

     

     

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

     

    Best Regards,

    Dedmon Dai

     

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous create two measure and in any visualization, add id and these measures and it should work

 

Total Sum = SUM ( Table]Column] )

Max Value = MAX ( Table[Column] )

Count = COUNTROWS ( Table )

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for the very quick reply parry2k! 

I think your solution is giving me two results per ID, a max and a sum, and I was looking for a single one.

I did work out a solution by splitting the problem - create a set of results for the anonymized data which sums scores, and a set of results for the non-anonymized data which takes max values, then combine the results. 

So my question was more to help my understanding of whether there is a way of conditionally applying a function during aggregation of data, ie IF <condition> THEN <sum values> ELSE <take max value> 

Still trying to figure out the concepts in Power BI.

Thanks again

Andrew

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous yes you can do conditional measure, here is an idea

     

    Conditional Measure = 
SWITCH ( SELECTEDVALUE ( Table[WhatMeasure] ),
"SUM", SUM ( Table]Column] ),
"MAX", MAX ( Tab;e[Column2] ),
"COUNT", COUNTROWS ( Table )
)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks parry2k 

       

      I've been playing around with this but failed in my attempts with using an aggregation function within the switch, the error message being that a "single value for column 'ID' couldn't be determined. 

       

      Here's a sample for the source and desired results I'm looking for, also the incorrect code I used.  I can kind of see what's going wrong, but not sure how to reformulate it - any ideas?

       

       

       

       

       

       

       


      Summary = SUMMARIZECOLUMNS('Source'[ID],
      "Count",COUNT('Source'[ID]),
      "Total",SWITCH('Source'[ID],
      "anon",SUM(Source[Score]),
      MAX('Source'[ID]))) //If not "anon" MAX is what I need

      Thanks

      Andrew

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Would you please try the following dax:

         

         

        Table 2 = SUMMARIZE('Table','Table'[ID],"Count",COUNT('Table'[ID]),"total",IF('Table'[ID] = "anon",SUM('Table'[Score]),MAX('Table'[Score])))

         

         

         

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

         

        Best Regards,

        Dedmon Dai