Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I am quite new to PowerBI and I can not seem to figure out the following.
I have a DIM table of "Installs" a DIM table of "Modules" and a FACT table of "Modules per Install".
for examble.
Installs
InstallID | InstallName |
1 | Lorem |
2 | Impsum |
3 | Bacon |
Modules
1 | Workshop |
2 | Invoicing |
3 | Rental |
4 | Warehousing |
Facts
InstallID | ModuleID |
1 | 2 |
1 | 3 |
2 | 1 |
2 | 2 |
2 | 4 |
3 | 2 |
I would love to find a way to have a report user select 1 or more modules and get all Installs that have ALL the selected modules.
Not an accumlation of all installs that have one of the selected which is what happens by default when using a slicer, barchart and table for example. So it act's and AND not OR
So when selecting Invoice & Rental I get Lorem. Now I get Lorem & Ipsum.
I am aware of the fact that once filtering, the barchart holds few value because all values would result in the same values.
To get an idea of the visual
Thank you so much
Solved! Go to Solution.
Hi, @woony
The method in @Greg_Deckler 's blog seems amazing and that seems to work, but for me as you said, the DAX part is hard to understand and I still don't understand why it works. I need to take time to figure it out.
I try to solve the problem myself and here is my solution.
to create a measure to get the Name:
_NAME =
VAR _Module_ID=SUMMARIZE(ALLSELECTED('Modules'),[module_ID])
var _Install_ID=SUMMARIZE(FILTER(ALL(Facts),'Facts'[InstallID]=MAX('Facts'[InstallID])),[ModuleID])
var _except=EXCEPT(_Module_ID,_Install_ID)
var _count_NOT_ID=COUNTROWS(_except)
var _theID=IF(_count_NOT_ID=BLANK(),0,_count_NOT_ID)
var _if=IF(_theID=0,
CALCULATE(MAX('Installs'[InstallName]),FILTER(ALL('Installs'),'Installs'[InstallID]=MAX('Facts'[InstallID]))))
return _if
Count_Name =
var _t=SUMMARIZE(ADDCOLUMNS('Facts',"_name",[_NAME]),[_name])
return COUNTAX(_t,[_name])
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @woony
The method in @Greg_Deckler 's blog seems amazing and that seems to work, but for me as you said, the DAX part is hard to understand and I still don't understand why it works. I need to take time to figure it out.
I try to solve the problem myself and here is my solution.
to create a measure to get the Name:
_NAME =
VAR _Module_ID=SUMMARIZE(ALLSELECTED('Modules'),[module_ID])
var _Install_ID=SUMMARIZE(FILTER(ALL(Facts),'Facts'[InstallID]=MAX('Facts'[InstallID])),[ModuleID])
var _except=EXCEPT(_Module_ID,_Install_ID)
var _count_NOT_ID=COUNTROWS(_except)
var _theID=IF(_count_NOT_ID=BLANK(),0,_count_NOT_ID)
var _if=IF(_theID=0,
CALCULATE(MAX('Installs'[InstallName]),FILTER(ALL('Installs'),'Installs'[InstallID]=MAX('Facts'[InstallID]))))
return _if
Count_Name =
var _t=SUMMARIZE(ADDCOLUMNS('Facts',"_name",[_NAME]),[_name])
return COUNTAX(_t,[_name])
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@woony I created an AND slicer once:
Patient Cohort (AND Slicer) - Microsoft Power BI Community
Hi Greg,
Thanks for your reply, looks like that should do it. It does feel complicated for something quite trivial in my opinion.
I'm looking into maybe some easier approaches from my database, but if that doesn't work I'll go with your approach.
@woony I think there are some other AND slicer solutions floating around out there. And yes, that particular bit of DAX is actually something that most people, even DAX experts, can't explain why it works. I explain it in detail in DAX Cookbook. It relies on a particularly quirky interaction between GENERATE and CALCULATETABLE.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.