multiple if multiple conditions

[vbis]

Hello.

I would need your help please with a bit complex multiple if statement with multiple conditions where I struggle with some issues. 

This is my dax:

Rates =

SWITCH(

    TRUE(),

    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Small", "5",
    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Medium", "10",
    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Large", "15",

    Clothes[Color] = "Grey" && [Size] = "Small", "5",
    Clothes[Color] = "Grey" && [Size] = "Medium", "10",
    Clothes[Color] = "Grey" && [Size] = "Large", "15",

    "null")

Issues: 

1. I want to assign specific rates in different and multiple buckets but the results are not the expected. 

   I start with the below for exmaple:  

Rates =

SWITCH(

    TRUE(),

    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Small", "5",
    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Medium", "10",

    Clothes[Color] = "Grey" && [Size] = "Small", "5",

    "null")

I get "5" in both small and medium sizes. But I also get "10" in the medium size as I should. 

2. If I write the below dax I get the message <ccon>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>

Rates =

SWITCH(

    TRUE(),

    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Small", "5",
    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Medium", "10",
    Clothes[Color] = "White" || Clothes[Color] = "Black" && [Units Sold] = "Low" && [Size] = "Large", "15",

    Clothes[Color] = "Grey" && [Size] = "Small", "5",
    Clothes[Color] = "Grey" && [Size] = "Medium", "10",
    Clothes[Color] = "Grey" && [Size] = "Large", "15",

    "null")

Is there something I am doing wrong? 

Thank you,

V

[v-yalanwu-msft]

Hi， @vbis ；

Perhaps you can reduce the performance of the data itself and optimize the performance. Or optimize the data itself in the power query for ETL data processing.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vbis]

Hello. 

Apparently, I still struggle with this. 

I reduced the volume of the data (total number of rows now 1,408,856) and I also tried to simplify a bit the formula and i still get invalid results. 

So, my dax now is:

Rates =

IF (

  [Units Sold]="Low" && Clothes[Color]="Grey" ||

  [Units Sold]="Medium",

    SWITCH ( [Size], "Small", "5", "Medium", "10", "Large", "15" ),

    "0")

For a number of rows i get for Medium 5 instead of 10 and for Large 10 instead of 15. 

What should i do to fix it?

[v-yalanwu-msft · Accepted solution]

Hi, @vbis ;

The last DAX can be simplified to and could try it.

Rates =
IF (
    ( Clothes[Color] IN { "White", "Black" }&& [Units Sold] = "Low" )
    || Clothes[Color] = "Grey",
    SWITCH ( [Size], "Small", "5", "Medium", "10", "Large", "15" ),
    "null")

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vbis]

Hello.

Thank you for your response. 

I tried this dax and i got the message:

"Something went wrong. There's not enough memory to complete this operation. Please try again later when there may be more memory available." 

[amitchandak]

@vbis , check the data in the Size column any white space or case(Upper/Lower)  issue. Overall formula looks correct

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.