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...
Need Help
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