Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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
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?
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
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |