cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WindAnalytics
Helper I
Helper I

Dynamic AND list

Hello,

 

I have the following table:

 

Product_IDDimensions
1Width=2
1Height=1
1Length=1
2Width=2
2Height=1
2Length=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.

 

CombinationNameCombination
Combo1Width=2
Combo1Height=1
Combo2Height=1
Combo2Length=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?

1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

Simple enough in spite that I personally dislike such non-star-schema model.

Screenshot 2021-11-30 205121.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

10 REPLIES 10
CNENFRNL
Super User
Super User

Simple enough in spite that I personally dislike such non-star-schema model.

Screenshot 2021-11-30 205121.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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

Screenshot 2021-11-30 213959.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

smpa01
Super User
Super User

@WindAnalytics  the combination table should contain all possible combinations of Width+Height+Length?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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.

@WindAnalytics  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 ) )

 

smpa01_0-1638303977203.png

pbix is attached

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@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.

@WindAnalytics  no worries mate !!! I misread the ask to start with @CNENFRNL  has a rather elegant solution 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


amitchandak
Super User
Super User

@WindAnalytics , 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/

https://radacad.com/pivot-and-unpivot-with-power-bi

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!