Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Jeevan1991
Helper III
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.

 

Please help me on this

 

@amitchandak 

@Geradav 

@Icey 

@Greg_Deckler 

 

 

 

 

 

 

 

 

 

 

 

 

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

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

10 REPLIES 10
Fowmy
Super User
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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy 

 

Let me try with some sample data

@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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy 

 

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

 

Thank you @Fowmy 

It is working fine, thanks a lot

floriangaerner
Regular Visitor

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.

 

Filter countries by city.jpg

amitchandak
Super User
Super User

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

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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors