Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
4 years ago
Solved

Switch statement with multiple conditions

  Hi,   I have two tables are Data and colour code. In data table and it’s contain the following columns are item, id type and colour code. In colour code table contain Id Type and Colour code.  ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Saxon10 

     

    Thanks for the Excel sample. I realized my previous column formula has a logic error so it didn't get the correct result. 

     

    I used another method which is similar to the logic in your Excel file. It may be easier to understand. Download the pbix file below. 

    Code Text = LOOKUPVALUE('Colour code table'[Text],'Colour code table'[Id Type],'Table'[Id Type])
 
Unique Count = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table','Table'[Item]=EARLIER('Table'[Item]) && 'Table'[Code Text]=EARLIER('Table'[Code Text])),"id_type",'Table'[Id Type])))
 
Result = IF('Table'[Code Text]="Not Pair" || 'Table'[Unique Count]=1, 'Table'[Colour Code], "Not Okay")
     
    Let me know if you have any questions. 
     
    Regards,
    Jing
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Saxon10 

 

If you want to have a calculated column, try below code. I used if statement. You can modify it into switch(true(),......) if you like. 

Column = 
VAR __items = DISTINCT(SELECTCOLUMNS(FILTER('Table','Table'[Item]=EARLIER('Table'[Item])),"id_type",[Id Type]))
VAR __number = COUNTROWS(__items)
RETURN
IF(__number=1,'Table'[Colour Code], IF((3000 IN __items && 4000 IN __items)||(5000 IN __items && 6000 IN __items), "Not Okay",'Table'[Colour Code]))

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Saxon10's avatar
Saxon10
Post Prodigy
4 years ago

Thanks for your reply and sorry for the late respones. Your soloutions almost working well but there is some error were mixed types. 

Could you please have a look when you have a time.

 

 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Saxon10 Try this code

    Column = 
VAR __items = DISTINCT(SELECTCOLUMNS(FILTER('Table','Table'[Item]=EARLIER('Table'[Item])),"id_type",[Id Type]))
VAR __number = COUNTROWS(__items)
RETURN
IF(__number=1 || 'Table'[Id Type] IN {7000, 8000}, 'Table'[Colour Code], IF((3000 IN __items && 4000 IN __items)||(5000 IN __items && 6000 IN __items), "Not Okay", 'Table'[Colour Code]))

     

    Regards,

    Jing