Measure Help

[JetMax]

Hi All!

I need some help with a Measure I am trying to do. I want to do the below which is working out the cost of something based on 3 separate critrea - Brand, Country and Type and work out the different combinations. I tried an IF or calculate but the problem is it is over two different datasets.

Dateset 1

Calculate(Sum(Total),filter(Description))

Dataset 2

Brand = Opt 1 or Opt 2

Country = Opt 1 or Opt 2

Type = Opt 1 or Opt 2

example-

(Calculate(Sum(Total),filter(Description))

*

if(Brand=Opt 1 and Country=Opt 1 and Type = Opt 1) 

or if(Brand=Opt 1 and Country=Opt 2 and Type = Opt 2) 

or if(Brand=Opt 1 and Country=Opt 1 and Type = Opt 2) 

or if(Brand=Opt 1 and Country=Opt 2 and Type = Opt 1) 

or if(Brand=Opt 2 and Country=Opt 1 and Type = Opt 1) 

or if(Brand=Opt 2 and Country=Opt 2 and Type = Opt 2) 

or if(Brand=Opt 2 and Country=Opt 1 and Type = Opt 2) 

or if(Brand=Opt 2 and Country=Opt 2 and Type = Opt 1) 

[amitchandak]

Try Like

SWITCH(true(),
firstnonbank(table[Brand])="Opt 1" &&  firstnonbank(table[Country])="Opt 1" && firstnonbank(table[Type]) = "Opt 1","A1",
firstnonbank(table[Brand])="Opt 1" &&  firstnonbank(table[Country])="Opt 2" && firstnonbank(table[Type]) = "Opt ","A3",
"A4"
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[JetMax]

Hi @amitchandak 

Thanks for the reply, I have tried this but I get this error - "Too few arguments were passed to the FIRSTNONBLANK function. The minimum argument count for the function is 2.". Have I entered somehing incorrectly?

[amitchandak · Accepted solution]

My Mistake

SWITCH(true(),
firstnonbank(table[Brand],blank())="Opt 1" &&  firstnonbank(table[Country],blank())="Opt 1" && firstnonbank(table[Type],blank()) = "Opt 1","A1",
firstnonbank(table[Brand],blank())="Opt 1" &&  firstnonbank(table[Country],blank())="Opt 2" && firstnonbank(table[Type],blank()) = "Opt ","A3",
"A4"
)