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

AND filter or Slicer visual

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

InstallIDInstallName
1Lorem
2Impsum
3Bacon

Modules

1Workshop
2Invoicing
3Rental
4Warehousing

Facts

InstallIDModuleID
12
13
21
22
24
32

 

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

environmentsbymodule.png

 

Thank you so much

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1637658780510.png

 

 

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.

 

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1637658780510.png

 

 

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.

 

Greg_Deckler
Super User
Super User

@woony I created an AND slicer once:

Patient Cohort (AND Slicer) - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors