cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NielsMortensen Frequent Visitor
Frequent Visitor

Bundling overlapping categories for filtering

Hi,

 

I'm new with DAX, so any help is highly appreciated. 

 

I'm trying to bundle various transaction categories, which I can then use for filtering in my reports. The problem is, that these bundles are overlapping each other with no underlying logic to exploit. In the sample table below, I want to be able to filter on the bundles x, y and z, where for instance the x bundle includes categories a, b, and c. The problem seems to ressemble a dynamic segmentation pattern, but instead of filtering on values, I need to filter on categories.

 

Any pointers as to how to go about this?

 

Categoryxyz
aTRUETRUEFALSE
bTRUEFALSEFALSE
cTRUETRUETRUE
dFALSEFALSETRUE

 

Cheers,

Niels

1 ACCEPTED SOLUTION

Accepted Solutions
NielsMortensen Frequent Visitor
Frequent Visitor

Re: Bundling overlapping categories for filtering

@ValubiMartin

 

I managed to solve the problem - thank you for providing the missing link Smiley Happy

 

I used your suggested table (execpt I filtered out all rows with IsTrue=0), as well as a new table with the unique categories organized in the below schema - note the bidirectional relationship between Categories and Bundles. Then I used the following measure in my visuals and sliced it with BundleID:

 

M2M =
CALCULATE(
    SUM(Facts[Amount]),
    CROSSFILTER(Categories[Category],Bundles[Category],Both)
)

 

schema.PNG

7 REPLIES 7
Super User
Super User

Re: Bundling overlapping categories for filtering

@NielsMortensen 

 

hi Niels

just to clarify what you are asking

 

you want to for example bring back all c categories where x is present

 

might be useful if you had a visual to explain what you expect to do. so one can understand the "to" part better , does that make sense?

 

 

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Highlighted
ValubiMartin Senior Member
Senior Member

Re: Bundling overlapping categories for filtering

Hi @NielsMortensen

 

From what I understand based on your explaination, you need to store your data differently in order to achieve your desired filtering mechanism. If I understand your challenge, and what you are trying to achieve, correctly, you should make a column, which could be called "Bundles", and then have duplicates on the category. You would then have 3 duplicates for each category, since you have 3 different bundles. You would also have a column called IsTrue to assign the corresponding value.

 

This is what I'm imagining would work out for you.

 

CategoryBundleIsTrue
ax1
ay1
az0
bx1
by0
bz0
cx1
cy1
cz1
dx0
dy0
dz1

 

Let me know if I've understood your problem correctly, and if you want me to elaborate on anything.

 

Best,

Martin

ValubiMartin Senior Member
Senior Member

Re: Bundling overlapping categories for filtering

@NielsMortensen

 

Did you solve your challenge?

NielsMortensen Frequent Visitor
Frequent Visitor

Re: Bundling overlapping categories for filtering

Here's an example of what I'm trying to do, with each category only belonging to one bundle. For this scenario, it's pretty straight forward. However, if for instance Category 'b' belonged to both the 'x' and 'y' bundles, the below approach wouldn't work because of the M:M relationship this introduces. So I guess my question is how I deal with the M:M relationship. I've found this article, which might help me, so I'll read up on that - https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/ 

bundle.PNGfacts.PNGSlicedDiagram.PNG

 

NielsMortensen Frequent Visitor
Frequent Visitor

Re: Bundling overlapping categories for filtering

@ValubiMartin, thank you for your suggestion, I think you're right about the data structure. I'll try it out, and give an update later.

NielsMortensen Frequent Visitor
Frequent Visitor

Re: Bundling overlapping categories for filtering

@ValubiMartin

 

I managed to solve the problem - thank you for providing the missing link Smiley Happy

 

I used your suggested table (execpt I filtered out all rows with IsTrue=0), as well as a new table with the unique categories organized in the below schema - note the bidirectional relationship between Categories and Bundles. Then I used the following measure in my visuals and sliced it with BundleID:

 

M2M =
CALCULATE(
    SUM(Facts[Amount]),
    CROSSFILTER(Categories[Category],Bundles[Category],Both)
)

 

schema.PNG

ValubiMartin Senior Member
Senior Member

Re: Bundling overlapping categories for filtering

 @NielsMortensen

 

Fantastic I'm glad you solved the problem with my help - Don't forget to give kudos! Smiley Wink

 

Could I ask you to accept a solution, so other people can benefit from it as well?

 

Best of luck in the future!

 

Best,

Martin