cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## How to filter state sales when we select the city from that state

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.

1 ACCEPTED SOLUTION
Super User

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up
10 REPLIES 10
Super User

@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])
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper III

Hi @Fowmy

Let me try with some sample data

Super User

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper III

Hi @Fowmy

Can you please explain how this DAX working, if you have 2 minutes?

Helper III

Thank you @Fowmy

It is working fine, thanks a lot

Regular Visitor

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.

Super User

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))

Helper III

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.

Super User

@Jeevan1991 , if your table is joined with your table on state with city table

Helper III

Hi Amit,

We have only 2 tables Sales Table (Fact) and State Table (Dimension)