Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@Anonymous

 

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

 

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

 

Anonymous
Not applicable

Hi @Anonymous

 

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

Anonymous
Not applicable

@Anonymous

 

Did you solve your challenge?

Anonymous
Not applicable

@Anonymous

 

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

 

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

Anonymous
Not applicable

 @Anonymous

 

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

Anonymous
Not applicable

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

vanessafvg
Super User
Super User

@Anonymous 

 

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?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.