cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lundy
Regular Visitor

Dynamically Calculated Table

I have two tables, State and City.  They are related one-to-many with the state column:

 

State Table:

state
Colorado
Illinois

 

City Table:

statecity
ColoradoBoulder
ColoradoFt Collins
IllinoisChampaign
IllinoisUrbana

 

I would like to create a 3rd table called City2.  City2 should should be dynamically calculated based on filter context of the State table.  If one state from the State table is selected, City2 should include all rows from City which are associated with the selected state.  If more than one state is selected, City2 should contain zero rows.

Is this possible, and if so, what would the DAX code look like for City2 ?

 

Thank you!

2 REPLIES 2
amitchandak
Super User
Super User

@lundy , You can not create such a calculated table as the calculated table does not use slicer values.

 

You need to create a measure and use that with city

 

measure =

var _1 = int(isfiltered(State[State]), allselected())

var _2 = countx(allselected(State), State[State])

return

if(_1=1 && _2 =1, count(City[City]) , blank())



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Thank you for your response @amitchandak !

Just to clarify, Did you mean for your code to look like this?

measure =
var _1 = int(isfiltered(State[State])) //, allselected())
var _2 = countx(allselected(State), State[State])
return
if(_1=1 && _2 =1, count(City[City]) , blank())

I see that the measure you suggest contains the proper number of City rows when a single State is selected, and that it contains Blank() when multiple States are selected. Thank you for that.  What I'm after, however, is for a City Table to be dynamically created based on the filter context of the State table. Not sure how I would accomplish that with the measure you suggest.  Is there by chance a suggestion on that?

Possibly you gave me the answer already--"You can not create such a calculated table as the calculated table does not use slicer values.".  Is that the case ?

I find myself repeatedly needing this functionality in many projects. What I'm trying to accomplish is to display Facts (Cities in this case) when a single Dimension is selected (ie State), yet hide Facts when multiple Dimensions are selected.  An elegant way to accomplish this might be to conditionally hide or display the City Table Visual based on the value of a measure like the one you suggest. This Microsoft Idea (powerbi.com) has been proposed for exactly that functionality, but has yet to be accepted by Microsoft.

 

Interested in your thoughts.  Thank you!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors