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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors
Top Kudoed Authors