Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi Friends,
Interviewer today asked me below question
We have a CITY slicer and we have LINE CHART (Datewise), once we select the CITY from slicer LINE CAHRT should show selected CITY's STATE values in LINE CHART.
For an example: If we select BANGALORE in CITY slicer LINE CHART should show KARNATAKA state values in LINE CHART.
Note: We need to achieve this without DAX measure.
Please help me on this
Solved! Go to Solution.
@Jeevan1991
I made a sample file, in my example, I took Category and Product, similar to State and City.
Please find it below my signature.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Jeevan1991
Before you answer this question, either ask how the data model is set up or you provide an assumption.
Suppose you have a data model having a dimension table for Geography with State and City, a dates table, and the fact table. Geography is connected to your fact table on City and the dates table is also connected to the fact table both using one-to-many relationships.
The question is how to get the related State sales for the city selected and show on the line chart.
Answer: Use CALCULATE function to calculate the TOTAL_SALES measure by modifying the filter context. Remove the filter from Geography[City] using REMOVEFLTERS and apply the filter on the state using VALUES fucntion for the selected city.
The measure should be:
State Sales =
CALCULATE(
[Sales_Total],
REMOVEFILTERS(Geography[City]),
VALUES(Geography[State])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Jeevan1991
I made a sample file, in my example, I took Category and Product, similar to State and City.
Please find it below my signature.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Jeevan1991
If you do not want to do it with a measure like @amitchandak suggested, you would need to have a snowflake like in the image below. So, you can use the CountryName in your line chart and have a slicer on CityName, which filters the available countries for the line chart.
Measure =
var _st =allselected(City[State]) // as city and state in same table, I can get state
return
calculate(sum(Table[Value]) , filter(all(city), City[State] in _st))
Hi Amit,
Thanks for the soluton, but he has asked me the logic without wirtting the DAX function, only DAX i can use is TOTAL_SALES.
Hi Amit,
We have only 2 tables Sales Table (Fact) and State Table (Dimension)
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
102 | |
68 | |
45 | |
37 | |
36 |