Forum Discussion

vsorensen's avatar
vsorensen
Frequent Visitor
4 years ago

DAX SELECTVALUE() not recognizing dynamic measure

Hi,

 

I'm having an issue where the value returned by SELECTEDVALUE can be compared to a hardcoded value but not a measure. Below is the code that I've written. 

 

 

Planned Contingency Spending = 
IF(
    SELECTEDVALUE('BAC EAC Data (COBRA)'[FY]) <= [FiscalYear],
    BLANK(),
    CALCULATE(
        SUM(
            'Contingency-Funding Profile'[Planned Contingency Spending]
        ),
        'BAC EAC Data (COBRA)'[FY] = MAX('BAC EAC Data (COBRA)'[FY]
        )
    )
)

 

 

 

The measure [FiscalYear] returns the value "FY22 Q3". 

 

As written, the IF() statement evaluates to false no matter what the 'BAC EAC Data (COBRA)' [FY] value is, even if it is less than the [FiscalYear] value. If I replace [FiscalYear] with a hardcoded value of "FY22 Q3" the code runs as desired and there is no sum calculated for fiscal quarters equal to or before FY22 Q3. I need to have this code be dynamic so that it does not have to be updated each quarter. Is there anyway around this?

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    vsorensen what is the expression for Fiscal Year measure, seems like it is getting evaluated in the row context and the value is changing. Maybe the visual you are using, drop Fiscal Year measure in that visual and check what value you are getting.

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

    • vsorensen's avatar
      vsorensen
      Frequent Visitor
      4 years ago

      The FiscalYear measure is not in the visual itself. Instead I have a column in the data set called FY and that is in the x-axis. 

       

      Here is the code for the FiscalYear measure:

       

      FiscalYear = 
      VAR CurrentPeriod = 
          CALCULATE(
              MAX('BAC EAC Data (COBRA)'[Date]),
              FILTER('BAC EAC Data (COBRA)', 'BAC EAC Data (COBRA)'[ACWP] > 0))

      VAR _Month = MONTH(CurrentPeriod)

      VAR _Year = RIGHT(YEAR(CurrentPeriod), 2)

      RETURN
      IF(
          _Month >= 10 && _Month <= 12,
          "FY" & _Year + 1 & " Q1",
          IF(
              _Month >= 1 && _Month <= 3,
              "FY" & _Year & " Q2",
              IF(
                  _Month >= 4 && _Month <= 6,
                  "FY" & _Year & " Q3",
                  "FY" & _Year & " Q4"
              )
          )
      )
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    vsorensen your reply is very confusing, you are saying it is a column and at the same time you said here is the expression for the measure?

     

    Could you please read your reply and explain it clearly? Thanks!

    • vsorensen's avatar
      vsorensen
      Frequent Visitor
      4 years ago

      I apologize for the confusion

       

      'BAC EAC Data (COBRA)' [FY] is a column and that is what I use for the x-axis in my visual

       

      FiscalYear is a measure that determines the current fiscal year and quarter (i.e. "FY22 Q3")

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， vsorensen ；

    It can be compared with the metric value, but since the metric value changes dynamically; so you can put [FiscalYear] in the table to see if each row returns what is the result?

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.