Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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