Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Category | x | y | z |
a | TRUE | TRUE | FALSE |
b | TRUE | FALSE | FALSE |
c | TRUE | TRUE | TRUE |
d | FALSE | FALSE | TRUE |
Cheers,
Niels
Solved! Go to Solution.
@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)
)
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/
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.
Category | Bundle | IsTrue |
a | x | 1 |
a | y | 1 |
a | z | 0 |
b | x | 1 |
b | y | 0 |
b | z | 0 |
c | x | 1 |
c | y | 1 |
c | z | 1 |
d | x | 0 |
d | y | 0 |
d | z | 1 |
Let me know if I've understood your problem correctly, and if you want me to elaborate on anything.
Best,
Martin
@Anonymous
Did you solve your challenge?
@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)
)
@Anonymous
Fantastic I'm glad you solved the problem with my help - Don't forget to give kudos!
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, thank you for your suggestion, I think you're right about the data structure. I'll try it out, and give an update later.
@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?
Proud to be a Super User!
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |