Forum Discussion

woony's avatar
woony
Frequent Visitor
4 years ago
Solved

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

 

Thank you so much

 

Need Help
Tutorial Requests
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

     

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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's avatar
      woony
      Frequent Visitor
      4 years ago

      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. 

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        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.