Forum Discussion
Dynamic AND list
Simple enough in spite that I personally dislike such non-star-schema model.
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
smpa01, thank you for the quick reply. That article you wrote looks really interesting. However I am not interested in generating evey possible combination just because they exist. The Product_IDs have their properties and I'm only interested in identifying Product_ID that have a specific combination of these propeties. I make this list of products that are interesting to me.
If we take your example of the list, it is made up of powerBI, powerQuery, DAX (the dimensions). Now imagine that all those keywords identify the software called Power BI (the product). Imagine additional dimenions such as: dataLoadingScript, setAnalysis, etc. Those would identify another product called Qlik Sense. Imagine also that there are thousands of products in this list all with their own keywords. So what I want to do is to identify a specific product which not only has one of those properties but multiple at the same time. So in my imaginary Combinations list I might be interested in identifying software that meets the following combination: powerQuery and DAX. That would identify Power BI and Excel but not SSAS service which is only limited to DAX.