Forum Discussion
Post ProdigySwitch 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
Post Prodigy
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.
Community SupportHi 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")- Saxon10
- v-jingzhang
You are welcome