cancel
Showing results for
Did you mean:
Regular Visitor

## Dynamically Calculated Table

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

State Table:

City Table:

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
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
Regular Visitor

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!

Announcements

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

#### 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!

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