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

     

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 !