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
Cars10
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
Anonymous
Not applicable

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

 

 

 

View solution in original post

9 REPLIES 9
luxpbi
Helper V
Helper V

Hi Cars10,

 

Try with this: 

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

Hello luxpbi, 

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

 

table_problem2.PNG

Anonymous
Not applicable

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

 

 

 

Anonymous
Not applicable

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

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

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

Anonymous
Not applicable

Hi Angelia,

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

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

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

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.