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

[Anonymous]

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.

[Anonymous]

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.

 

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. 😞

[mochabits]

@Anonymous  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

[Anonymous]

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!

[Anonymous]

Hi @Anonymous, 
did you get a chance to solve this problem? I'm facing the same problem and i'd like to know how did you get though this

[mochabits]

@Anonymous  

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

Try using selectedvalue() instead of values()

[parry2k]

@Anonymous 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])