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:
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?
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.
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.
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.