cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Measure with STRING output not being recognised as parameter for FILTER()

Hi Folks,

 

This problem has plagued me for a couple of days now and I'm increasingly losing my grip on reality. Send help!

 

The simplified mechanics of the issue is as follows (I've had to anonymize the data, so forgive me if there is some silliness/ambiguity in table/column names):

1. I have three slicers. One slicer allows the user to select a country. Another selects a state. The third selects a city/town.

 

2. I have 'purchase_table' that looks like this:

 

'purchase_table'
PurchaseID | Country | State | City/Town

1                   USA         CA       San Francisco

2                   AUS         NSW   Sydney

...                   ...             ...         etc.


3. In order to work out what 'level of region' should be shown on the nearby chart, I have written a multi-if measure in 'purchase_table', [regionLevelSelection]:

regionLevelSelection = 
if(isfiltered('purchase_table'[City/Town]), "City/Town",
if(isfiltered('purchase_table'[State]),"City/Town",
if(isfiltered('purchase_table'[Country]),"State",
"Country")))

 

4. Alongside this, I created an unpivoted version of 'purchase_table':

 

'purchase_table_unpivot'
PurchaseID | Region | Category
1                   USA       Country
1                   CA         State
1                   San...     City/Town

...                   ...          ...

 

5. There is the DAX-driven table, 'purchase_table_unpivot_filtered' to hone the unpivot alongside [regionLevelSelection]:

 

purchase_table_unpivot_filtered = FILTER('purchase_table_unpivot','purchase_table_unpivot'[Category] = [regionLevelSelection])

 

6. I have the purchase amounts themselves in 'value_table':


'value_table'
PurchaseID | Amount
1                   1000
2                   2000

...                  ...

 

MY PROBLEM:

 

In the 'purchase_table_unpivot_filtered' DAX, when I replace:
purchase_table_unpivot_filtered = FILTER('purchase_table_unpivot','purchase_table_unpivot'[Category] = [regionLevelSelection])
with
purchase_table_unpivot_filtered = FILTER('purchase_table_unpivot','purchase_table_unpivot'[Category] = "Country")

or

purchase_table_unpivot_filtered = FILTER('purchase_table_unpivot','purchase_table_unpivot'[Category] = "State")

or

purchase_table_unpivot_filtered = FILTER('purchase_table_unpivot','purchase_table_unpivot'[Category] = "City/Town")

 

A bar chart that shows the total purchase value for the regions works when I put these exact strings into the DAX, but when [regionMeasureSelection] is used, it's ALWAYS showing the "City/Town" level, as if the multi-if has hit TRUE straight away.

 

However, I have a card that displays [regionLevelSelection] and it CONSISTENTLY shows the correctly selected [Category] based on the state of the slicers, so the multi-if is working perfectly fine.

 

How can I parse the string contained in [regionLevelSelection] into the DAX for 'purchase_table_unpivot_filtered' so it filters the value of [Category] that can be seen in the card?

 

Bless you, sweet saviour.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Measure with STRING output not being recognised in FILTER() in another table

So after posting on reddit, stack overflow, calling Microsoft consultants all to no avail, I contacted a Microsoft Partner.

Their response was pretty grim: 

"...a table created by DAX is not dynamic but is fixed in content when the model initially builds..."

 

So we had to figure a workaround.

You can find the solution in the mock-up google drive folder:
https://drive.google.com/drive/folders/1fnVEK3FJ_e0dB7GHxHa35pTZS1EzUoul?usp=sharing

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Measure with STRING output not being recognised in FILTER() in another table

You may have luck reversing the order of your checks. If you filter Country, then of course State and City/Town are filtered, they are filtered via the Country. Not sure ISFILTERED would be your most reliable indicator but you have a lot going on in this post and it is kind of hard to follow exactly what you are attempting to do here.

 

You might also try something ISINSCOPE or even get the count of ALL of your City/Town and compare the current count to it. Lots of different ways do this. I've had luck with HASONEVALUE for example. And, I would use a SWITCH TRUE statement rather than nested IF's. Nested IF's should almost never be used in polite society.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: Measure with STRING output not being recognised in FILTER() in another table

The order of the checks is definitely correct. I know that multi-ifs are a living nightmare but because the parameter being checked on each level was different, I didn't know how to write a switch to solve it ☹️

 

But I know the result of the multi-if is correct because the card I made showing output of [regionLevelSelection] is correct every time.

 

The problem lies in actually parsing that string into the FILTER().

 

I know the whole method should theoretically work because when I enter "Country", "State" or "City/Town" into the FILTER(), the resulting table is exactly what I need. The 'purchase_table_unpivot_filtered'[Region] column is then used in a bar chart as its axis. I use 'value_table'[Amount] as the chart's value (yes, the model is all set up to facilitate this). When I use this static method, this chart behaves exactly as I want it to depending on the static string I've used.

 

But when I use [regionLevelSelection] as the FILTER() parameter, it always shows the "City/Town" level, directly contrasting the result in the card.

Highlighted
Resolver II
Resolver II

Re: Measure with STRING output not being recognised in FILTER() in another table

HI @Anonymous 

Any chance you can share the pbix of your mock-up?

I can't promise anything but having the mock-up would probably increase the number of responses on the forum

Thanks, Brian

Highlighted
Anonymous
Not applicable

Re: Measure with STRING output not being recognised in FILTER() in another table

Hi @bpsearle 

 

Fantastic suggestion! Below is the link to the mock-up .pbix and data I have created. 

https://drive.google.com/drive/folders/1fnVEK3FJ_e0dB7GHxHa35pTZS1EzUoul?usp=sharing

 

Thank you!

Highlighted
Anonymous
Not applicable

Re: Measure with STRING output not being recognised in FILTER() in another table

So after posting on reddit, stack overflow, calling Microsoft consultants all to no avail, I contacted a Microsoft Partner.

Their response was pretty grim: 

"...a table created by DAX is not dynamic but is fixed in content when the model initially builds..."

 

So we had to figure a workaround.

You can find the solution in the mock-up google drive folder:
https://drive.google.com/drive/folders/1fnVEK3FJ_e0dB7GHxHa35pTZS1EzUoul?usp=sharing

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors