Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.
 
Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous  

     
     [Selected Report Period 1] = selectedvalue('Revenue (TH)'[Report Period])
    Try using selectedvalue() instead of values()
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you parry2k  and Anonymous 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. :(

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        Hello Anonymous,

        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!