Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This might be a bit hard to explain, so I'll correct as needed.
I have a table with three columns all containing Territory information, but representing three different territory groupings, as follows:
Territory 1 | Territory 2 | Territory 3 |
NY | NY | CA |
NJ | PA | WA |
PA | DE | PA |
ME |
In my report, I would like to create a single slicer that works across all three territories. So if I am looking at Sales data for PA, I would like to select PA from a singler slicer and have it apply to any visualization that contains PA in any of the three territory groupings. I have tried extracting these into a separate table and then joining on that table three times. Did not work. I tried creating three versions of that table and joining on each, but again this did not work.
I was thinking I could create a single column that contains potentially contains all three values, and then only displays the one value that is represented in the slicer (e.g. return PA if it exists in any of the three columns), and then I could join on that single column. But I can't figure out how to do this in DAX - even though it would not be hard to do in Excel formulas using concatenate and find).
Any thoughts on if/how this can be done?
Best,
Scott
Solved! Go to Solution.
@Anonymous
Let me help you with your question:
1: Create a New Table with this Dax:
Territories = DISTINCT ( UNION ( SELECTCOLUMNS ( Table1, "T", Table1[Territory 1] ), SELECTCOLUMNS ( Table1, "T", Table1[Territory 2] ), SELECTCOLUMNS ( Table1, "T", Table1[Territory 3] ) ) )
Use this table as slicer.
2. Create a measure:
ShowRow = IF ( HASONEVALUE ( Territories[T] ), IF ( COUNTROWS ( FILTER ( Table1, Table1[Territory 1] = VALUES ( Territories[T] ) ) ) || COUNTROWS ( FILTER ( Table1, Table1[Territory 2] = VALUES ( Territories[T] ) ) ) || COUNTROWS ( FILTER ( Table1, Table1[Territory 3] = VALUES ( Territories[T] ) ) ), 1, 0 ) )
Use this measure as Visual Filter in a table with your other 3 columns.---to show only when is 1.
I hope this help you.
Example in Power BI Desktop:
Table TeSt (Territory State) with fields Territory and State. Rows for each combination (so 10 rows).
Table State with field State and 1 row for each state.
Table SalesByState with fields State and Sale
Create relationships:
State - TeSt on field State
State - SalesByState on field State
Make sure the relationship State-TeSt has "Cross filter direction" set to "both" in Manage Relationships - Edit Relationship.
Now you can create a slicer on territory and it will work as intended.
@Anonymous
Let me help you with your question:
1: Create a New Table with this Dax:
Territories = DISTINCT ( UNION ( SELECTCOLUMNS ( Table1, "T", Table1[Territory 1] ), SELECTCOLUMNS ( Table1, "T", Table1[Territory 2] ), SELECTCOLUMNS ( Table1, "T", Table1[Territory 3] ) ) )
Use this table as slicer.
2. Create a measure:
ShowRow = IF ( HASONEVALUE ( Territories[T] ), IF ( COUNTROWS ( FILTER ( Table1, Table1[Territory 1] = VALUES ( Territories[T] ) ) ) || COUNTROWS ( FILTER ( Table1, Table1[Territory 2] = VALUES ( Territories[T] ) ) ) || COUNTROWS ( FILTER ( Table1, Table1[Territory 3] = VALUES ( Territories[T] ) ) ), 1, 0 ) )
Use this measure as Visual Filter in a table with your other 3 columns.---to show only when is 1.
I hope this help you.
Thanks Vvelarde!
That works to filter results when selecting a territory, is there a way to also allow a SELECT ALL? So in this example, you would be able to use the slicer to Select All territories or to allow for multi-select?
Hello there, I have same question too. Let say the table is:
Territory 1 | Territory 2 | Territory 3 |
0.23 | 0.45 | 0.65 |
0.32 | 0.5 | 0.7 |
0.4 | 0.56 | 0.75 |
Then, I want to put in one slicer, show list of :
Slicer :
Territory 1
Territory 2
Territory 3
Then, when I click Territory 1, the data in territory will display on line chart. Then, If I click Territory 2, then the line chart will display territory 2 data only.
Hope you can help me. thank you in advance.
What is "T" in the measure?
Thanks
Kg
Hello guys,
i would like to filter a Waterfall report with a slicer like the following:
Slicer WaterFall Report
2010 2009 and 2010
When selected a Year value in the slicer show in the report the corresponding Year records and Year-1 records
Any Idea to reach this?
Thanks in advance
This has little to do with the original topic.
Please create your own topic, so it will stand out as not solved yet and having zero replies.
That worked! Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |