Switch statement with multiple conditions

[Saxon10]

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.

1. If same item contain the following types 3000 and 4000 and two different colour code then return Not Okay. If not then return colour code. It can’t be two different colour code for the same item with following types 3000 and 4000.

     2. If same item contain the following types 5000 and 6000 and two different colour code then return Not Okay. If not then         return colour code. It can’t be two different colour code for the same item with following types 5000 and 6000.

    3.If same item contain the following types 7000 and 8000 then the colour code must be Orange. It can’t be two different colour code for the same item with following types 7000 and 8000.

Data 

Item	Id Type	Colour Code	Desired Result
123	3000	Orange	Orange
124	4000	Green	Green
125	5000	Orange	Orange
126	6000	Green	Green
127	7000	Orange	Orange
128	8000	Orange	Orange
135	3000	Orange	Not Okay
135	4000	Green	Not Okay
136	5000	Orange	Not Okay
136	6000	Green	Not Okay
137	3000	Orange	Orange
137	7000	Orange	Orange
137	8000	Orange	Orange
138	4000	Green	Green
138	7000	Orange	Orange
138	8000	Orange	Orange
139	3000	Orange	Orange
139	7000	Orange	Orange
140	4000	Green	Green
140	7000	Orange	Orange
141	5000	Orange	Orange
141	7000	Orange	Orange
141	8000	Orange	Orange
142	6000	Green	Green
142	7000	Orange	Orange
142	8000	Orange	Orange
143	4000	Green	Green
143	6000	Green	Green
143	7000	Orange	Orange

Colour code table

Id Type	Colour Code
3000	Orange
4000	Green
5000	Orange
6000	Green
7000	Orange
8000	Orange

[v-jingzhang]

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

[Saxon10]

@v-jingzhang ,

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.

https://www.dropbox.com/s/59jd4o157y22o2s/Switch%20statement%20with%20multiple%20conditions-Jing-23-...

https://www.dropbox.com/scl/fi/fv1rgu0tqytd5hn46fn2m/Vlookup-countifs-20-09-21-NFS-R.xlsx?dl=0&rlkey...

[v-jingzhang · Accepted solution]

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]

@v-jingzhang 

Thanks for your reply and modifying the DAX formula according to Excel formula.

This is really amazing and easy to ready and understand the power BI functionality.

Much appreciated your help.

[v-jingzhang]

You are welcome

[v-jingzhang]

Hi @Saxon10 

If you want to have a calculated column, try below code. I used if statement. You can modify it into switch(true(),......) if you like. 

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'[Colour Code], IF((3000 IN __items && 4000 IN __items)||(5000 IN __items && 6000 IN __items), "Not Okay",'Table'[Colour Code]))

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Saxon10]

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]

@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

[MFelix]

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]

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.