Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Variable parameter in DAX formula

Hello everyone,

 

I have an issue that I am struggling with and I hope that you can help me with my issue. 

In Power BI I want to calculate whether there is a certain exceedance or not. For that I want to use a 'Checkdate'. If the provided date is smaller than the checkdate, then it is an exceedance, otherwise it is not an exceedance.

The simple form of the DAX formula I am talking about is shown below:

 

Exceedance=
IF (Finishdate.[Date]<Checkdate && Date2.[Date]<Checkdate;"Yes";"No")

 

Now I want to make the Checkdate a parameter that my fellow colleagues can adjust in the Power BI Service environment.

 

However, I don't know how to make it work. At the moment I have implemented the Checkdate as a refreshdate, which is implemented by using the date of today (by using UTCNow()), however, that does not fit this solution anymore because the Checkdate has to be dynamic in the report. 

I also don't want to hard code the date of Checkdate in all the similar formulas I have implemented. The Checkdate should be controlled by only 1 parameter.

 

(What I have also tried is to make a datetable in which a calendar function is used to show all the dates starting from beginning of 2018 until the end of 2019. And then I tried to define a measure and use the filter measure as the Checkdate parameter. This does work in a Card visual (which I want to use to show the Checkdate), but it does not act that way in the DAX formula.)

 

I hope my description of the issue is clear to you.

Could you help me?

 

Kind regards,

 

Jasper Sival

Need Help

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Jasper,

     

    A new Date table could be the best choice, but there is something to consider. 

    1. No relationship should be created.

    2. There could be no selection or multi-selection. Try a measure like below, please.

     

    Exceedance =
VAR checkDate =
    IF (
        SELECTEDVALUE ( 'Calendar'[Datekey] );
        MIN ( 'Calendar'[Datekey] );
        TODAY ()
    )
RETURN
    IF (
        MIN ( Finishdate.[Date] ) < Checkdate
            && MIN ( Date2.[Date] ) < Checkdate;
        "Yes";
        "No"
    )

    3. It should be a measure rather than a calculated column.

     

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Dale,

       

      thank you for your response. I have confirmed that your solution works when I want to define a a measure this way. Somehow it doesn't seem to work for calculated columns which is what is necessary to implement the full solution. 

      Is there a similar approach by using dynamic parameters which can be used for calculated columns?

      If so, how?

       

      I look forward to hearing from you.

       

      Kind regards,

       

      Jasper

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Jasper,

         

        The calculated columns can't respond to the slicer. So we can't do it like the measure. There could be a workaround. Please refer to the snapshot. We can add a new column in the Query Editor with parameters. Finally, you can change the parameter. But it can't as dynamic as a slicer.

        Variable-parameter-in-DAX-formula

         

        Best Regards,
        Dale