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. 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"
Table matrix custom representation - using any possible DAX queries
Hi All, I have the below source table as my input which is a table matrix with a set of 5 coulmns namely :- Name ,city, school,Age,type etc. Input source table: Name city school Age type A London ABC 12 Permanent B Berlin ADE 10 Temporary Output expected: Name city A London school ABC Age 12 type Permanent Name city B Berlin school ADE Age 10 type Temporary Is it possible to get the expected output in the given format above .Each row should breakdown into row and column[contianing the row values].Is it possible through any DAX assuming the row headers and column headers wont change anytime in future and no new enteries will be added. Kind regards Sameer
How do I validate that an ID exists in another table?
Hello All, I have a table that has multiple ID in one table. For example, one table will have the ID multiple times with different data. ID Data_1 Data_2 1 1 2 2 2 The other table is much smaller and only has the ID once. ID Data_3 Data_4 1 2 3 4 5 I would just like to return "Yes" if it does. "No" if it doesn't. I would like to do this with a calculated column so I have the ability to filter. Thank you in advance!
