Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have this measure that I have created that works fine with one caveat.. When using the measure on the y=axis and 'dim date'[month name] on the x-axis it display correctly, however..
When a filter is applied to the 'supported business' table for sector this reduces the running total amount correctly but for any months where the chosen sector value doesnt have a value the the x axis doesnt show that month, skips it and continues to the next month..
here is image with no filter applied..
here is mimage with sector filter applied..
and here is my measure..
Solved! Go to Solution.
Hi @Sparramanga ,
Please try this measure.
A SB Cumulative Deal Amount Previous FY =
Var maxdate =
calculate(
max('Supported Business'[RR If Cp Else GI Date])
,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31),REMOVEFILTERS('Supported Business'[Sector])
)
Var Result =
calculate(
sum('Supported Business'[RR Liability GBP])
, allselected('Supported Business')
,'Supported Business'[RR If Cp Else GI Date] <=maxdate
,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31)
)
return
Result
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brilliant thanks! That seems to do exactly what i wanted on first look 🙂
What is the reasoning behind removing filters in the maxdate variable?
I was trying several context filters in the calculate expression but to no avail.
And was even thinking about creating a dummy table with a value of zero for at least one date of each month and adding this total to the existing total to make sure all dates had an entry against them.. but thought there must be a simple solution.
Thanks,
R
Brilliant thanks! That seems to do exactly what i wanted on first look 🙂
What is the reasoning behind removing filters in the maxdate variable?
I was trying several context filters in the calculate expression but to no avail.
And was even thinking about creating a dummy table with a value of zero for at least one date of each month and adding this total to the existing total to make sure all dates had an entry against them.. but thought there must be a simple solution.
Thanks,
R
Hi @Sparramanga ,
REMOVEFILTERS() can clear filters from the specified tables or columns.
You can refer to the following documents that may be helpful to you:
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn
REMOVEFILTERS() in DAX - Excelerator BI
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yeah i will check the documentation again.. i think i was unsure as to why removefilters on the sector column would still let me filter the runing total amount by sector, so it reduces the total correctly but still keep the months on the x axis in tact.
Hi @Sparramanga ,
Please try this measure.
A SB Cumulative Deal Amount Previous FY =
Var maxdate =
calculate(
max('Supported Business'[RR If Cp Else GI Date])
,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31),REMOVEFILTERS('Supported Business'[Sector])
)
Var Result =
calculate(
sum('Supported Business'[RR Liability GBP])
, allselected('Supported Business')
,'Supported Business'[RR If Cp Else GI Date] <=maxdate
,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31)
)
return
Result
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |