Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@Jeevan1991 , if your table is joined with your table on state with city table
Hi Amit,
We have only 2 tables Sales Table (Fact) and State Table (Dimension)
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |