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.
Say I got a sales table, how do I set a filter so that the displayed month = max (sales date)? EG if the most recent transaction is June 2016, set the filter to monthyear = June 2016.
Hi @Anonymous,
In your scenario, you can create a measure to get the most recent date. Then create a custom column into the table to tell us which rows have most recent date. Then add this new column on Visual/ Page/ Report filter property. For more information, please refer to this blog: Finding the Latest Date in Power BI Desktop .
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
@v-qiuyu-msft Thanks this came in with a blog page which looked very promising as to resolving what I need because I can use the filter in the page or report level. However, I was stopped when trying to create the custom column. I got the error "function 'Calculate' is not allowed as part of calculated column DAX expressions on DirectQuery mode". I guess the solution only works under import mode?
Hi @Anonymous,
There are a set of DAX functions that have been disabled by default in DirectQuery mode. This is done in order to avoid performance issues with these functions when working in DirectQuery mode, as these calculations can get very expensive to run in the server.
In your scenario, if you want to allow “unrestricted measures” in DirectQuery mode, you can enable this option in the Options dialog.
Reference:
Ability to create measures when using DirectQuery mode
Best Regards,
Qiuyun Yu
Hi @Anonymous,
Based on my test, the CALCULATE() function is not available in custom column DAX expression, which is the same as error stated "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.". In your scenario, please use connection mode as Import mode instead of DirectQuery mode then follow the steps in the article to finish your job. And you can click Refresh button to get the latest data.
Best Regards,
Qiuyun Yu
Hi @Anonymous
You can do following :
1) Create measures which will return Month of last transaction i.e. MAXMONTH = Month(MAX(Date) and another for Year i.e. MAXYEAR= YEAR(MAX(DATE))
2) Now in your calculations you have to put filter condition for filtering example
FILTER(SalesTable,Month(SalesTable[Date])=MAXMONTH && Month(SalesTable[Date])=MAXYEAR )
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |