variable not working in the filter function of a measure column

[pohshiqing]

Hi,

Have a quick question on the following script entered in a measure column.

When i wrote the actual version below, the result is wrong, when I wrote a test version further below, the result is correct. I don't know what is the issue. Please assist! Thanks!

Actual Version

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (

    CALCULATE(

        SUM(TableA[WithDrawAmount]),

        FILTER(all(TableA),datevalue( TableA[Date])= date(m,2,27) && TableA[Status]= "Approved")

    )

)

Test Version 1

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (

    CALCULATE(

        SUM(TableA[WithDrawAmount]),

        FILTER(all(TableA),datevalue( TableA[Date])= date(2017,2,27) && TableA[Status]= "Approved")

    )

)

Test Version 2 (m is 2017 which is correct)

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (m)

[v-ljerr-msft]

Hi @pohshiqing,

I just tested your formulas with some sample data, and the variable works all fine in the actual version of your measure(gets the same result as Test Version 1).

To further investigate on this issue, could you share a sample pbix file which can reproduce it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[pohshiqing]

Hi,

Thanks for your suggestion. Unfortunately I managed to create a test pbix and it is working for the imported data but not for direct query data. Here are my findings:

1. The script works for year but not month.

2. The script works for import excel sheet but not direct query sql tables.

What could cause this issue because of the directquery?

[v-ljerr-msft]

 Hi @pohshiqing,

According to your description above, I just tested the formulas against a SQL DB with DirectQuery mode. And it turns out that the formulas also work fine for both YEAR and MONTH in DirectQuery mode. 

It's a little wired that only YEAR works but MONTH not in the same scenario. So I would assume there may be some logic differences between your formulas(actual and test version) for MONTH in DirectQuery mode(that's all I can think now).

Regards

[pohshiqing]

Hi,

Have a quick question on the following script entered in a measure column.

When i wrote the actual version below, the result is wrong, when I wrote a test version further below, the result is correct. I don't know what is the issue. Please assist! Thanks!

Actual Version

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (

    CALCULATE(

        SUM(TableA[WithDrawAmount]),

        FILTER(all(TableA),datevalue( TableA[Date])= date(m,2,27) && TableA[Status]= "Approved")

    )

)

Test Version 1

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (

    CALCULATE(

        SUM(TableA[WithDrawAmount]),

        FILTER(all(TableA),datevalue( TableA[Date])= date(2017,2,27) && TableA[Status]= "Approved")

    )

)

Test Version 2 (m is 2017 which is correct)

Last Approved Bank Transfer Out =

var m = year(

    datevalue(

        calculate(max(TableA[Date]),all(TableA),TableA[Status] = "Approved")

    )

)

return (m)