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 the following measures
(specifics of this first one shouldn't matter?)
Capped Partner Month = if([sum of rule charge] > 1.1*[average charge all partners],1.1*[average charge all partners],[sum of rule charge])
and
rolling 12 capped Partner Month2 = Sumx( DATESBETWEEN( rule[Start of Month], SAMEPERIODLASTYEAR(LASTDATE(rule[Start of Month])), LASTDATE(rule[Start of Month])),[Capped Partner Month])
This gets me
That gets a great rolling 12 IF that were the first month with any data at all. However what this shows is the first month of data not filtered out by page.
There IS data from the month prior, it's just not shows on this table because I have a page level filter that is showing only months where the status is "Partner".
What i'm looking to do is ignore that filter, and get the rolling 12 for the first month highlighted using the data that is not shown (filtered out) on this table. I've tried different versions of all(), removefilters() etc but I'm too new to DAX to know where to insert and keep getting syntax errors.
Trying things like
rolling 12 capped Partner Month3 = Sumx( removefilters(DATESBETWEEN( rule[Start of Month], SAMEPERIODLASTYEAR(LASTDATE(rule[Start of Month])), LASTDATE(rule[Start of Month]))),[Capped Partner Month])
Gets syntax error
and trying
rolling 12 capped Partner Month3 = CALCULATE(Sumx( DATESBETWEEN( rule[Start of Month], SAMEPERIODLASTYEAR(LASTDATE(rule[Start of Month])), LASTDATE(rule[Start of Month])),[Capped Partner Month]),KEEPFILTERS(rule))
did something really wierd... it ignored the results of the if/then in [Capped Partner Month] and just went back to showing me the result of [Sum of rule charge] ?!?.
So, i'm over my head. Appreciate any help. Thanks!
I've now tried every possible combination of all, removefilter, calculate, etc that I could think off. I'm starting to think this isn't possible.
I'm going to try getting rid of all the filters on the page, and try incorporating them into the DAX expressions. Maybe that will work.
So for anyone interested, here's what I found that works
SUMX ( DATESBETWEEN ( rule[Start of Month], SAMEPERIODLASTYEAR ( LASTDATE ( rule[Start of Month] ) ), LASTDATE ( rule[Start of Month] ) ), CALCULATE ( [sum of rule charge], ALL ( rule[Partner Status] ) ) )
It didn't seem to me that that this should work, but it does. The display still only shows rows that aren't filtered out by the page filter, but the calculation contains all data.
Here was my mental hang up on even trying the above:
SumX is SUMX(<table>, <expression>). In the formula, I pictured SumX iterating through a limited table that only included the last 12 months, but ALSO that the table was limited by the page filters etc. Therefore, any <expression> that followed would be limited to only viewing what was part of that limited table - no matter what I tried to do to expand the context of the <expression>. So, I wrongly surmised, I had to remove the page level filters from the table or the <expression> could never ever 'see' it.
Hence me trying things like sumx(Filter(datesbetween(*****),All(Rule[Partner Status])),<SimpleExpression>) where I was trying to get the table to expand to include my data.
I hope I'm understanding that correctly and explaining that correctly, and hope it helps someone.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |