Forum Discussion
Dynamic AND list
Hello,
I have the following table:
| Product_ID | Dimensions |
| 1 | Width=2 |
| 1 | Height=1 |
| 1 | Length=1 |
| 2 | Width=2 |
| 2 | Height=1 |
| 2 | Length=2 |
I now want to return all Product_ID where Dimension = "Width=2" and Dimension = "Height=1" are true at the same time. How do I accomplish this with a third table called Combinations where these Combinations are pre-set? The Combinations is shown below and the criteria are not necessarily limited to two rows but could be one or three rows.
| CombinationName | Combination |
| Combo1 | Width=2 |
| Combo1 | Height=1 |
| Combo2 | Height=1 |
| Combo2 | Length=2 |
This purpose of this table is to use it in a slicer, more specifically the CombinationName column. So when I select Combo1 or Comobo 2, how do I return all the Product_IDs which meet all of the criteria listed for that Combo?
Simple enough in spite that I personally dislike such non-star-schema model.
10 Replies
Anonymous , In power query
1. Split Dimensions column by delimiter (=) , into new column
2. Then pivot the two columns, you should get new columns like Height and width
then you can use them as filter, if this can work
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Hello amitchandak and thank you for your suggestion. The problem is that I have more than one hundred of different properties in the column I called Dimension for each Product_ID. Is there any way to make it work in the current form?
Anonymous the combination table should contain all possible combinations of Width+Height+Length?
Hello smpa01, the combination table simply contains combinations of dimensions I might be interested in. The first table that contains Product_ID has thousands of actual products and each product has more than one hundred unique properties. Width, Height, Length were just three of those properties. Having the Combinations table is to enable me to identify Product_IDs with specific combinations of dimensions/properties with one click.
Anonymous I wrote a blog post on generating all possible combination of a list
In your case you can generate the combination table like this
let comb= (x as list)=>let Initiator={{}}, Loop = List.Generate( ()=>[i=0,j=x{i},k=List.Combine({Initiator{i},{j}}),l=List.InsertRange(Initiator,List.Count(Initiator),{k})], each[i]<List.Count(x), each[i=[i]+1,j=x{i},k=[l],l= let x = List.Generate( ()=>[a=0,b=k{a},c=List.Combine({b,{j}}),d=List.Combine({k,{c}})], each [a]<List.Count(k), each [a=[a]+1,b=k{a},c=List.Combine({b,{j}}),d=List.Combine({[d],{c}})], each[d] ) in x{List.Count(x)-1}], each [l] ) in List.Transform(Loop{List.Count(Loop)-1},each Text.Combine(_,",")), Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Dynamic-AND-list/m-p/2214745#M809121"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(5) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(5) > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product_ID", Int64.Type}, {"Dimensions", type text}}), Dimensions = List.Sort(List.Distinct(#"Changed Type"[Dimensions])), Custom1 = List.Select(comb(Dimensions),each List.Count(Text.Split(_,","))>1), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "combIndex", 1, 1, Int64.Type) in #"Added Index"then you can write a measure liek this to give you what you need
Measure = VAR _comb = CONCATENATEX ( FILTER ( combinations, combinations[Column1] = SELECTEDVALUE ( combinations[Column1] ) ), combinations[Column1], ",", combinations[Column1] ) VAR _max = CALCULATE ( CONCATENATEX ( 'fact', 'fact'[Dimensions], ",", 'fact'[Dimensions] ), ALLEXCEPT ( 'fact', 'fact'[Product_ID] ) ) RETURN CALCULATE ( MAX ( 'fact'[Dimensions] ), FILTER ( 'fact', _max = _comb ) )pbix is attached
Simple enough in spite that I personally dislike such non-star-schema model.
Hello CNENFRNL thank you for your help. In reality it is a star schema model. I just wanted to simplify it here 🙂 Nonetheless your solution just might work for my purposes (I will try it tomorrow) and it definetly works for the challenge I wrote about here as your pbix clearly demonstrates, so I'll accept it as the solution! Thank you once again!
A solution a bit more robust if there were, by mistake, duplicates in PRODUCT table.
Product_IDDimensions
1 Width=2 1 Height=1 1 Length=1 2 Width=2 2 Height=1 2 Length=2 2 HEIGHT=1
