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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, that was the solution, many thanks v-deddai1-msft !