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
MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi Saxon10 ,

 

Not sure if I understand it correctly but if an item has two different colours then should return Not okay right?

Or do you have cases when you have ID with 3000 /  6000 so would have two colours but from different codes.

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago

Thanks for your reply and your both statements right.

 

If two different colour code for same item then result is not okay only the following id types 3000, 50000, 6000. Excluded for 7000 and 8000.