Forum Discussion

PaulKraemer's avatar
PaulKraemer
Advocate II
1 year ago
Solved

Referencing a user controlled variable in a measure

Hi,

 

The link below is to a .pbix file with a single matrix visualization that includes a Measure (PctDiff) that computes the percentage difference between an Actual Value and a Target Value.  I have another Measure (Comparison) that returns True if PctDiff is greater than or equal to 0.25 (25%), and otherwise returns False.  

 

UserControlledVariableInMeasure.pbix

 

Rather than hard-coding the percentage on which this comparison is based at 25%, I'd like to give the user of my report the ability to choose this percentage.  

 

My Comparison measure is currently defined as follows:

 

Comparison = IF([PctDiff]>= 0.25, TRUE(), FALSE())
 
... Can anyone tell me if rather than hard-coding the value 0.25 into the Comparison measure, if there is a way I can reference a variable here, and give the user the ability to set this variable elsewhere on my report page?
 
Thanks in advance,
Paul

 

 

Need Help
  • Deku's avatar
    Deku
    1 year ago

    File didn't open for me

     

    I create a table, and named the column Percentages.

     

    Percentage = GENERATESERIES(0, 100, 1)

     

    And a measure

     

    Target = 
var percentage = SELECTEDVALUE( Percentage[Percentages] ) / 100
RETURN
SUM( 'Table'[Value] ) * percentage

     

    Add a slicer for the user to select from and add the measure a table.

     

     

6 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    You need to have a list of values you'd allow to be selected in a table. 

     

    For percentage this could be generated with

     

    Percentages =

    Generateseries(0,100,1)

     

    This would be added a slicer to the report and you could pick it up in your measures with

     

    Selectedvalue( percentages[value] ) / 100

    • PaulKraemer's avatar
      PaulKraemer
      Advocate II
      1 year ago

      Thank you Deku - that sounds like a great approach, but I must be doing something wrong.  

       

      In the updated file at the link below, I created a calculated table named Percentages as you suggested, and I added a slider that allows the me to select a value from this table.  I changed my Comparison measure to reference SELECTEDVALUE(Percentages[Value])/100 instead of the hard-coded value 0.25, but the value I select in the slider does not seem to have an effect on the the results of my Comparison, which always returns True now.  As a test, I added a measure named PercentSelected defined as follows:

       

      PercentSelected = SELECTEDVALUE(Percentages[Value])
       
      When I added this measure to my matrix visualization, I hoped it would show the selected value in every row in the Matrix, but it is blank for every row.
       
      Here is the link to my updated file, if you don't mind taking a look at it...
       

      If you can give me a clue what I am missing, I would greatly appreciate it.
       
      Thanks again,
      Paul

       

       

      • Deku's avatar
        Deku
        Super User
        1 year ago

        File didn't open for me

         

        I create a table, and named the column Percentages.

         

        Percentage = GENERATESERIES(0, 100, 1)

         

        And a measure

         

        Target = 
var percentage = SELECTEDVALUE( Percentage[Percentages] ) / 100
RETURN
SUM( 'Table'[Value] ) * percentage

         

        Add a slicer for the user to select from and add the measure a table.

         

         

  • PaulKraemer's avatar
    PaulKraemer
    Advocate II
    1 year ago

    Thank you Deku - I just updated to the Feb 2025 release, and I still don't have the Single Value option.  

     

    I think that is a different question than my original post.  I will accept your solution for this one and post another question about the Single Value thing.  

     

    I appreciate your help!

     

    Best regards,
    Paul

  • PaulKraemer's avatar
    PaulKraemer
    Advocate II
    1 year ago

    Thank you Deku - I now realize that my problem was how I configured my slicer.  Rather than selecting a single value, I had it selecting a range of values.  The Style options I see are the following...

     

     

    With this set to Dropdown, I am able to select a single value as shown below, and my Comparison logic works as you suggested it would...

     

     

    I like what you did in your example better.  It looked like a single entry field (labeled Percentages) below with a cicular control on a horizontal line that you can drag to change the value...

     

     

    Would you mind telling me the Slider settings you used to accomplish this?

     

    I appreciate your help.

     

    Best regards,
    Paul

     

     

     

    • Deku's avatar
      Deku
      Super User
      1 year ago

      Single Value

       

      Not sure why you can't see it. I am using Feb-25 release of Power BI