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.
Hello everyone, I would like to be able to filter my report on a country and state level.
When I filter on country level, I get the sum of targets(as expected) but when I select a single state the value does not change and this is not the expected behavior)
Any help would be greatly appreciated.
Please see an attached pbix file for reference. Thank you in advance.
https://drive.google.com/file/d/1GmOHGFDiN_KHdnzAJakd8y6frq4bG-xK/view?usp=sharing
Solved! Go to Solution.
Hi @sloane ,
You could use the following formula:
Measure =
VAR endOfPeriod =
MAX ( 'Calendar Table'[Date] )
VAR startOfPeriod =
MIN ( 'Calendar Table'[Date] )
RETURN
CALCULATE (
SUM ( 'Country Target'[Sales diff (targets)] ),
FILTER (
'Country Target',
'Country Target'[date ] >= startOfPeriod
&& 'Country Target'[date ] <= endOfPeriod
&& 'Country Target'[state] IN ALLSELECTED ( 'Country table'[state] )
)
)
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sloane ,
You could use the following formula:
Measure =
VAR endOfPeriod =
MAX ( 'Calendar Table'[Date] )
VAR startOfPeriod =
MIN ( 'Calendar Table'[Date] )
RETURN
CALCULATE (
SUM ( 'Country Target'[Sales diff (targets)] ),
FILTER (
'Country Target',
'Country Target'[date ] >= startOfPeriod
&& 'Country Target'[date ] <= endOfPeriod
&& 'Country Target'[state] IN ALLSELECTED ( 'Country table'[state] )
)
)
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @mahoneypat
The state slicer filters the country slicer but not the line graph. It still shows me the country sum
Please share your new pbix with the single 1:M relationship between those tables on state (or concatenated state-country columns).
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
In the query editor, highlight the State column on your Country Table and choose Remove Duplicates. Then you can use a 1:Many relationship on just the State columns (delete the one on Country). Then both slicers (Country and State) should work. If you happen to have two countries with the same state name, you can then concatenate Country and State together in both tables and use those columns in your relationship.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |