Variable parameter in DAX formula

[Anonymous]

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

[v-jiascu-msft]

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]

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]

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.

Best Regards,
Dale