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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Anonymous
Not applicable

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!

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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