Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 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
Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost 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
Icon for Community Support rankCommunity 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