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

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors