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

DAX: SLICER to reduce data plus a FILTER that shows all data

Hello Power People,

 

I work with a Dealers Table of the following content (Table1):

 

ID	District
3	11
5	12
6	11
8	13
9	11
12	12
15	13

I then add a slicer to enable the User to select one ID of the list. The list reduces to one line.

 

Now I want to show in a new table all other IDs of the same District. I.E. if the User chooses ID = 6 I want to present all the rows from District 11 (ID = 3,6,9). 

 

If I add the measure:

District_selected = LOOKUPVALUE(Tabelle1[District]; Tabelle1[ID]; SELECTEDVALUE(Tabelle1[ID]))

and add a card to the report it will show the correct District (here 11). If I then create my final table with the following command:

 

District_table = FILTER(ALL(Tabelle1); Tabelle1[District] =  Tabelle1[District_selected])

it will show ALL districts instead of just the one that I expected to see. Can anybody help me with this? 

This pic shows all elements in one report

 

table_problem.PNG

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
martynbooth88 Regular Visitor
Regular Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

So I think I've got this working. Here's the steps I took:

 

1 - Created a measure to hold the selected value:

 

District_selected = SELECTEDVALUE(Table1[District], 0)

 

2 - Created a 2nd table with only a unique list of Districts.

 

3 - Created a 3rd table to hold the Selected Districts data:

 

Table3 = FILTER(ALL(Table1), Table1[District] = [District_selected])

 

4 - Joined the tables together by District. See the screenshots below:  

Districts Results.JPG

 

Districts Table Join.JPG

 

 

 

9 REPLIES 9
Highlighted
luxpbi Member
Member

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hi Cars10,

 

Try with this: 

District_table = FILTER(ALLSELECTED(Tabelle1); Tabelle1[District] =  Tabelle1[District_selected])
Cars10 Frequent Visitor
Frequent Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hello luxpbi, 

thanks a lot for answering my question, but the different Districts still appear in the result:

 

table_problem2.PNG

martynbooth88 Regular Visitor
Regular Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

So I think I've got this working. Here's the steps I took:

 

1 - Created a measure to hold the selected value:

 

District_selected = SELECTEDVALUE(Table1[District], 0)

 

2 - Created a 2nd table with only a unique list of Districts.

 

3 - Created a 3rd table to hold the Selected Districts data:

 

Table3 = FILTER(ALL(Table1), Table1[District] = [District_selected])

 

4 - Joined the tables together by District. See the screenshots below:  

Districts Results.JPG

 

Districts Table Join.JPG

 

 

 

Cars10 Frequent Visitor
Frequent Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hello martynbboth88,
 
thank you very much for your detailed answer.   But the Table1 is part of a Data Warehous which is loaded on a regular basis.
It might be that a new import brings new District numbers so that I had to recreate Table2 also (step 2 in your description).
Although this solution  might work I'm looking for something that I could do with DAX only. 
To my understanding CALCULATE alters the Filter context so it should be possible to remove the Slicer Filter and see all data from Table1.  
I'm still struggeling with the DAX language.
 
Regards
v-huizhn-msft Super Contributor
Super Contributor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hi @Cars10,

For your scenario, you need to create a calculated table based on slicer, it's impossible to achieve it in Power BI desktop. Please refer to the basic knowledge: Calculated Column/Table Change Dynamically According to Slicer Selection in the Report

Best Regards,
Angelia

Cars10 Frequent Visitor
Frequent Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hello Angelia, thank you so much for you answer.

The link you added concludes with the result that instead of using a table it is possible to use measures that eventually are placed in a matrix. So that part of the soulution is easy. But I can't think of a measure that strips off the filter from the slicer and adds instead another filter to the result. Isn't that something that CALCULATE should do?

 

Best regards,

Cars10

v-huizhn-msft Super Contributor
Super Contributor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hi @Cars10,

The filter for a visual is used to filter the row of resource table, while the filter function in CALCULATE impact the result of a measure, which can not control the showing data.

Best Regards,
Angelia

chahatkaur Frequent Visitor
Frequent Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hi martynbooth88

The solution you provided was perfect but could you help me with one slight addition in this question.

Please see  below my table1,

What i want is that i get all the records that have the same id as that of the name selected in slicer (in my case)but also the ones where id is blank.

 

thank you

 

myfile.png

chahatkaur Frequent Visitor
Frequent Visitor

Re: DAX: SLICER to reduce data plus a FILTER that shows all data

Hi Angelia,

I am unable to access your link. Could you provide it some other way?