topic Re: Dynamically Calculated Table in DAX Commands and Tips

Dynamically Calculated Table

lundy — Fri, 09 Sep 2022 23:16:05 GMT

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

State Table:

state

Colorado

Illinois

City Table:

state	city
Colorado	Boulder
Colorado	Ft Collins
Illinois	Champaign
Illinois	Urbana

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!

Re: Dynamically Calculated Table

amitchandak — Sat, 10 Sep 2022 02:13:29 GMT

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

Re: Dynamically Calculated Table

lundy — Sat, 10 Sep 2022 04:20:43 GMT

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!