cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaspersival Frequent Visitor
Frequent Visitor

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Variable parameter in DAX formula

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jaspersival Frequent Visitor
Frequent Visitor

Re: Variable parameter in DAX formula

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

Community Support Team
Community Support Team

Re: Variable parameter in DAX formula

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

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