Using a Case Statement Inside Summarise Columns

[StuMortimer]

Hi Everyone,

I'm new to DAX but have been writing SQL for years. I'm struggling to find a way to include a switch statement inside of a summarise columns statement. I basically want to calculate a category on the fly like this...

EVALUATE

SUMMARIZECOLUMNS(
     PRODUCT[PROCUCT CODE],
     PRODUCT[PRODUCT NAME],

    "Product Category", SWITCH(PRODUCT[PROCUCT CODE], "00001", "Category 1", "00002", "Category 2", "Otherwise Unknown")

)

I can put a calculated column in the tabular data model i'm using but that seems like overkill because I just need it for this one report i'm working on. In SQL, i'd have just used a case statement and that would have been the end of it 🙂

Does anyone have any advice?

Many Thanks, Stu

[ValtteriN · Accepted solution]

Hi,

Include MAX, MIN or SELECTEDVALUE to your SWITCH and you should be good to go. Example:

Table 6 = SUMMARIZECOLUMNS(ItemExample[Item],ItemExample[Column],"Switch",SWITCH(MAX(ItemExample[Item]),"Item1","1","Item4","4","other"))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/