Forum Discussion

admin_xlsior's avatar
admin_xlsior
Icon for Post Prodigy rankPost Prodigy
6 years ago

Selected date from DATE SLICER

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

Need Help

4 Replies

    • admin_xlsior's avatar
      admin_xlsior
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      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's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        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.