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
v-jiascu-msft Super Contributor
Super Contributor

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

v-jiascu-msft Super Contributor
Super Contributor

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 81 members 1,062 guests
Please welcome our newest community members: