Forum Discussion
Switch statement with multiple conditions
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
Hi Saxon10
Have you solved this problem? If so, kindly accept an appropriate post as solution or post your own solution to help other members find it. Otherwise, please provide more details about the problem so that we can work on it further. Thanks.
Best Regards,
Community Support Team _ Jing
Thanks for your replay again and sorry get back to you late respones becuase I am keep cross checking your DAX code with multiple example and I found some deviation on the result.
Some of the place still giving wrong result were multiple id types mixed againt the item (3000, 4000, 5000 and 6000)
The same item has two different colour code within the following id types 3000, 4000 || 4000 , 5000 then return "Not Okay" otherwise return colour code.
Could you please explain your logic so I can try to understand step by step.
In Excel I am applying below mentioned formulas to get my final output. Some reason it won't work the same logic in PBI but it's work in Excel.
D3=VLOOKUP($B3,$H$3:$J$8,3,0)
E3=SUMPRODUCT(1/COUNTIFS(A$3:A$101,A3,B$3:B$101,B3,D$3:D$101,D3))
F3=IF(OR($D3="Not Pair",SUMIFS(E$3:E$101,A$3:A$101,A3,D$3:D$101,D3)=1),$C3,"Not Okay")
I am attached Excel and PBI file link for your reference.
could you please help for this query when you have a chance.
Example- Here I mentioned the list of items are wrong result.
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,JingYou are welcome