Workaround for a slicer with AND logic

[Anonymous]

Hello everyone!

Hope you are doing fine and are healthy!

I am a novice at Power BI and DAX, but unfortunately I got to deal with pretty advanced stuff right of the bat.

There are two datasets:

• The whole base structure, which has the folders and measures and shows the hierarcy (for example,
  • Folder "Client"
    • Folder "Client Orders"
      • Measure "Last Order"
    • Measure "Client Number")
• The data with all the reports, which has the reports' names and measures used within them and the name of the folder each measure belongs to (for example, Report "Client Info by Client number" has measures "Client orders"/"Last Order" and "Client"/"Client Number"

After a quick work with initial data, I got the tables in the spreadsheets, which looks like this:

The most painful for me here is finding a way to create a baby between filter and slicer: I need the report to show a list of reports according to user measures' selection.

• Filter does not give the functionality I need; if I choose any measure from (for example) Level3, the filter would automaticaly exclude everything else. Also - only single selection is possible.
• Slicer, on the other hand, uses OR logic and adds all reports, which have at least one selected field, not only reports which include all selected items.

Ultimately, I strive for the next results:

• In case user selects Measure1.1, the report will show the next "Report Name": Users, Orders, Products:

• if user selects Measure1.1 and Measure1.3, the report will show: Orders, Products

• and if user selects  Measure1.1, Measure1.3 and Measure1.5, the report should show Products

I tried to play with ALLSELECTED variables and IN operators, but no luck when it actually comes to filtering the reports' table.

As for the connections in the data model: as far as I understood there is no need for connections, if you do not use regular slicers and filters.

I know, that this is a very out-of-the box issue, but maybe someone can show me the way I should be digging. Hope, someone had faced such a task before and can provide a few hints.

Thanks for any assistance provided.

Best regards,

Dmytro

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

From your statement, I think you want to show specific measures by select slicer.

Due to I don't know your data model and values, I build a sample to have a test.

Table:

Build a measure to show specific measure1.x as below. I use 1,2,3,4,5 to replace your measure code.

M.Measure = 
VAR _Measure1_1 = 1
VAR _Measure1_2 = 2
VAR _Measure1_3 = 3
VAR _Measure1_4 = 4
VAR _Measure1_5 = 5
RETURN
SWITCH(MAX('Table'[Measure]),"Measure1.1",_Measure1_1,"Measure1.2",_Measure1_2,"Measure1.3",_Measure1_3,"Measure1.4",_Measure1_4,_Measure1_5)

Slicer Table:

Measure:

Count = 
VAR _Sel = ALLSELECTED('Slicer'[Measure])
VAR _Count = IF(MAX('Table'[Measure]) in _Sel,1,0)
Return
_Count

Show items equal to 1 = 
    VAR _SelectMeasure = ALLSELECTED(Slicer[Measure])
    VAR _CountALL = SUMX(FILTER(ALL('Table'),'Table'[ReportName]=MAX('Table'[ReportName])),[Count]) 
    RETURN
    IF(_CountALL= COUNTROWS(Slicer)&&MAX('Table'[Measure]) in _SelectMeasure,1,0)

Build a table visual by ReportName, Measure column and M.Measure.

Then add Show items equal to 1 into the filter field in this table visual.

By default it will show blank.

Select Measure1.1

Select Measure1.1, Measure1.3

Select Measure1.1, Measure1.3, Measure1.5

You can download the pbix file from this link: Workaround for a slicer with AND logic

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[lbendlin]

two things come to mind

1. the idea of recreating a matrix from scratch https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...

2. have a look at Calculation Groups. It's a pretty advanced topic but it goes in the same direction as your described scenario.