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

[Jeevan1991]

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

@amitchandak 

@Geradav 

@Icey 

@Greg_Deckler 

[Fowmy]

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

[Jeevan1991]

Hi @Fowmy 

Let me try with some sample data

[Fowmy · Accepted 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.

[Jeevan1991]

Hi @Fowmy 

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

[Jeevan1991]

Thank you @Fowmy 

It is working fine, thanks a lot

[floriangaerner]

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.

[amitchandak]

@Jeevan1991 ,

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

[Jeevan1991]

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.

[amitchandak]

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

[Jeevan1991]

Hi Amit,

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