Forum Discussion

JetMax's avatar
JetMax
Frequent Visitor
6 years ago
Solved

Measure Help

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) 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    6 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      JetMax
      Frequent Visitor
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

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