cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aurasol Frequent Visitor
Frequent Visitor

Using a Measure (that results in a text string) in a Filter expression

Hi All,

 

I am using a measure that results in a text string to filter values in a column.

 

This is my DAX code, I am using the measure [Selected Report Period 1] which results in a text string to add only the values in the column with the resulting text string:

Gross Revenue 1 = CALCULATE([Gross Revenue (Selected Currency Period)], FILTER('Revenue (TH)','Revenue (TH)'[Report Period]=[Selected Report Period 1]))
 
In my data table, the column Revenue (TH)'[Report Period] contains the text values "2RE'19" & "JUL'19".
The measure [Selected Report Period 1] is the selected value in a Slicer and the DAX code for this is
VALUES('Revenue (TH)'[Report Period]).
 
Also if it helps, DAX code for Gross Revenue (Selected Currency Period) = [LC Gross Revenue '000]/[Selected Currency FX].
 
I have scoured all related posts in forums but there is none that I have found that is about using a text string result of a measure as a filter. By the way, if I hard code the text values in lieu of [Selected Report Period 1] in FILTER, I can get the expected results, but I want this to be dynamic, thus, sticking to using the measure as a filter.
 
Thank you to all who take time to help.
 
5 REPLIES 5
Super User
Super User

Re: Using a Measure (that results in a text string) in a Filter expression

@aurasol based on your explanation it should work, not sure what is going on there. try changing following measure and put this measure on a KPI card to see what value you get, just to confirm you are getting selected value.

 

[Selected Report Period 1] =
SELECTEDVALUE('Revenue (TH)'[Report Period])





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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





mochabits Regular Visitor
Regular Visitor

Re: Using a Measure (that results in a text string) in a Filter expression

@aurasol  

 
 [Selected Report Period 1] = selectedvalue('Revenue (TH)'[Report Period])
Try using selectedvalue() instead of values()
aurasol Frequent Visitor
Frequent Visitor

Re: Using a Measure (that results in a text string) in a Filter expression

Thank you @parry2k  and @mochabits for the quick response!

 

I used SELECTEDVALUE and yes it worked. But probably I might have a greater problem here because I am not able to arrive at my desired solution yet.

 

What I wanted to do is for each of the Revenue Categories shown in the Matrix rows below, I want to show a Gross Revenue totals column filtering the rows only for the selected Report Period (I will ultimately remove the Report Period field in the Matrix to show only Revenue Categories). I want the Report Periods to be dynamic, that is why I am using a measure in the FILTER condition.

 

 

 2.PNG

My ultimate goal is to add a column calculating the variance/difference between Gross Revenue 1 and Gross Revenue 2 columns. I also did not discover a fix to Calculating Column Grand Totals in a Matrix table as a Difference. Hence, I resort to writing a measure for Gross Revenue for each Report Period.

 

Below is also a screenshot of my Revenue (TH) table and table relationship diagram. Would really appreciate your help to resolve this as I have been trying to figure this out for a week now and can't do anything else. 😞

4.PNG3.PNG

Highlighted
mochabits Regular Visitor
Regular Visitor

Re: Using a Measure (that results in a text string) in a Filter expression

@aurasol  Since you want the report period to be the slicer, you don't need to explicitly specify it in the measure. So do you have a flag or column to indicate gross revenue 1 and 2? 

 

If yes, your two measures will be like

gross_rev_1 := calculate (gross revenue), filter ([revenue_type] = 1)

, and the other is - 

gross_rev_2 := calculate (gross revenue), filter ([revenue_type] = 2)

The third measure will be like

variance := gross_rev_1 - gross_rev_2
aurasol Frequent Visitor
Frequent Visitor

Re: Using a Measure (that results in a text string) in a Filter expression

Hello @mochabits,

If I hardcode the Report Type filter, the filtering will not be dynamic.
There is actually more than two report periods and I want the user to choose only 2 report periods at a time, hence the selection based on the slicer.
Thank you again for taking time to understand and respond. Really appreciate it!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,619 guests
Please welcome our newest community members: