Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
brief001
Helper II
Helper II

Return value based on a selection table

I have a table that shows what colors a record contains. Next, a selection table must be looked at to determine what value the record will receive.
It is possible that several selections will suffice, in which case it will be the choice with the lowest priority number.

brief001_0-1664268795928.png

 

 

 

Record 1 contains the colors red and white, and according to the selection table this record will be given the value red. This is because record 1 contains the color red.
Record 2 contains the colors yellow, green and purple, and according to the selection table this record will be given the value yellow-green. This is because record 2 contains the colors yellow and green.
Record 3 contains the colors brown, orange and black, and according to the selection table this record will be given the value brown-orange. This is because record 3 contains the colors bronw and orange.
Record 4 is a special one, just like record 3 it contains the colors brown and orange. But also contains the color blue. And via the selection table also contains exlude columns, which if blue appears, then further search is required. As a result, record 4 will have to get the value blue.
Record 5 is also special. This contains blue and red. And therefore meets two options according to the selection table. If multiple options are found, then the value must be given with the lowest priority number. In this case, the value will turn red.
Record 6 contains colors that do not appear in selection table. In this case, the value unknown must be given.

 

 

--- --- --- --- --- --- ---

 


Editor example voor table : Fact_table

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNzi9KUTBU0gEyU5RidVCEwjMyS1KRBY2AgpGpOTn55eii7kWpqXnoggGlRQU5KAYYA0WdivLL89AF/YsS89IxleYkJmcjC5pg02+CVT9YaU4pipgpDjE0v5th87sZkodiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Content = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"ID", type text}, {"Content", type text}})
in
#"Type gewijzigd"


Editor example voor table : Selection_table

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKTQGSWBFIKlYnWskIyI5MzcnJLwcy3ItSU/OwKoco0YUoAOkzBgo6FeWXg5T7FyXmpafC1cJMccopRQiC1epCVYIMMMFrH1wSpNQU3TA0BJaLjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Priority = _t, Contains_1 = _t, Contains_2 = _t, Contains_3 = _t, Contains_4 = _t, Exclude_1 = _t, Exclude_2 = _t, Exclude_3 = _t, Exclude_4 = _t, Value = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Priority", Int64.Type}, {"Contains_1", type text}, {"Contains_2", type text}, {"Contains_3", type text}, {"Contains_4", type text}, {"Exclude_1", type text}, {"Exclude_2", type text}, {"Exclude_3", type text}, {"Exclude_4", type text}, {"Value", type text}})
in
#"Type gewijzigd"

1 REPLY 1
v-cazheng-msft
Community Support
Community Support

Hi @brief001,

 

Not very clear about the calculated logic of Record 4 and Record 5. Could you please make it clearer? Thanks in advance!

 

Best Regards,

Community Support Team _ Caiyun

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.