Selected date from DATE SLICER

[admin_xlsior]

Hi ,

I have a requirement for Date selection in Power BI. Basicaly what we need is only 1 single date and this single date will be the base of many calculation in the future. 

At first I 'm using the comon Dropdown list form Date Slicer, then to catch the selected date I just create this measure :

'AsOfDate' = SELECTEDVALUE('Date'[Date])

But actually it is not nice to see a drop down with many date especially when we have years of date, for just selecting one date only.

Second, I tried to change the Date slicer layout to be "Before", the one that have slide below it and a From and To date. The reason to use Before, so we can use the Calendar Tool to fill in the date, but by uing "Before" we only need to fill in 1 date, which is the "To Date". So with that I create this measure :

'AsOfDate' = CALCULATE(MAX('Date'[Date])

It looks ok at first, BUT there is another probem, since I'm using Direct Query, when I'm using this measure 'AsOfDate', there is an error when I create calculate column, saying "Function MAX is not allowed as part of calculated column DAX expression on DirectQuery models"

Is there any other workaround I can follow to have that selected date "AsOfDate" ?

Thanks

[amitchandak]

Try like

'AsOfDate' = MAXX('Date','Date'[Date])

[admin_xlsior]

Hi,  it's the same error. "MAXX is not allowed as part of calculated column...."

I think this is related to "Unrestricted measures of Direct query". They used to have options to "Allow unrestricted measures for Diect Query" in Options and Setting, but this option is gone.

Thanks,

[dax]

Hi admin-xlsior,

When use Direct Query,  some function can't be used in calcuated column, you could refer to DAX formula compatibility in DirectQuery mode for details.

In addition, you said that you use "before" in slicer, I think which work like a calendat picker

You could choose date in calendar instead of use max date.

Best Regards,
Zoe Zhi

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

[admin_xlsior]

Hi,

Yes, the calendar tool is working fine. I can input using that or the slider. But I need to capture the value for my next formula. That's why I'm using MAX because I only want to get the 2nd value ("To" date)

That's is why I'm using DAX. But currently I get error because it is not allowed.

Thanks,