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
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
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
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    You are welcome