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.
I have a simple measure that calculates a monthly average amount by category using a variable as numerator and a variable as denominator. It works perfectly when set as:
Monthly Avg Amount = VAR Numerator = CALCULATE( SUM('All Transactions'[Transaction Amount]), DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN VAR Denominator = CALCULATE( DISTINCTCOUNT('Calendar'[Mmm'YY]), DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN DIVIDE(Numerator,Denominator,0 )
However, I have one more filter criteria I'd like to add to the numerator. I want to exclude data rows marked as Outliers with an "x". So my formula becomes:
Monthly Avg Amount = VAR Numerator = CALCULATE( SUM('All Transactions'[Transaction Amount]), DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR), FILTER('All Transactions','All Transactions'[Outlier]<>"x")) RETURN VAR Denominator = CALCULATE( DISTINCTCOUNT('Calendar'[Mmm'YY]), DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN DIVIDE(Numerator,Denominator,0 )
Adding this line appears to restrict the entire daterange of the numerator calculation to the current month (the denominator remains unaffected). Why would adding this additional filter undo the relative date filtering function?
Hi @Anonymous ,
All function will break filter effect on table, please use allselected function to instead.
In addition, you can take a look at following blog to know more about how to use 'all' function:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
Please understand this link is provided with no warranties or guarantees of content changes, and confers no rights.
Regards,
Xiaoxin Sheng
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 |
---|---|
107 | |
93 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |