Forum Discussion
Question of Filter the Measure
Hello,
Below is my current measure that displays the cumulative CheckFeatures by WeekStartingDate. It works correctly when no additional filters are applied:
CumulativeCheckFeatures =
VAR CurrentWeekEndSK =
CALCULATE(
MAX(DatesTable[DateSK]),
DatesTable[WeekStartingDate] = MAX(DatesTable[WeekStartingDate])
)
RETURN
CALCULATE(
SUM(Features[CheckFeatures]),
FILTER(
ALL(Features),
Features[CreatedDateSK] <= CurrentWeekEndSK
)
)
However, when I apply an AreaSK filter (for example, filtering to AAAA), the chart does not display the expected values. With the AAAA filter, the chart currently shows one column on January 26, 2025, with a value of 15; the X-axis then lists February 09 and February 23 with no columns; followed by a column on March 09, 2025 with a value of 22.
For your reference, here is some sample data from our DatesTable:
DateSK WeekStartingDate
.......................
20250126 1/26/2025 12AM
20250127 1/26/2025 12AM
20250128 1/26/2025 12AM
20250129 1/26/2025 12AM
20250130 1/26/2025 12AM
20250201 1/26/2025 12AM
20250202 2/02/2025 12AM
..............
sample Features table
WorkItem CreatedDate CreatedDateSK AreaSK CheckFeatures WeekStartingDate
40001 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40002 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40003 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40004 1/27/2025 1272025 BBBB 1 1/26/2025 12AM
40005 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40006 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40007 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40008 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40009 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40010 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40011 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40012 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40013 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40014 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40015 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40016 1/27/2025 1272025 AAAA 1 1/26/2025 12AM
40017 2/20/2025 2202025 AAAA 1 2/16/2025 12AM
40018 2/23/2025 2232025 AAAA 1 2/23/2025 12AM
40019 2/27/2025 2272025 AAAA 1 2/23/2025 12AM
40020 3/04/2025 3042025 BBBB 1 3/02/2025 12AM
40021 3/04/2025 3042025 BBBB 1 3/02/2025 12AM
40022 3/04/2025 3042025 BBBB 1 3/02/2025 12AM
40023 3/04/2025 3042025 BBBB 1 3/02/2025 12AM
40024 3/07/2025 3072025 AAAA 1 3/02/2025 12AM
40025 3/07/2025 3072025 AAAA 1 3/07/2025 12AM
40026 3/13/2025 3132025 AAAA 1 3/09/2025 12AM
40027 3/13/2025 3132025 AAAA 1 3/09/2025 12AM
40028 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40029 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40030 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40031 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40032 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40033 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40034 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40035 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
40036 3/18/2025 3182025 AAAA 1 3/16/2025 12AM
Other work items have a CheckFeatures value of 0, so they should not affect the chart. I use CreatedDateSK to map with DateSK.
Could you please help me update the script so that when I apply the AAAA filter, the cumulative values display as follows on the X-axis:
- January 26, 2025: 15
- February 02, 2025: 15
- February 09, 2025: 15
- February 16, 2025: 16
- February 23, 2025: 18
- March 02, 2025: 20
- March 09, 2025: 22
- March 16, 2025: 31
Thank you very much for your help.
9 Replies
- Khushidesai0109
Skilled Sharer
Hiii yzhouMC
Your current measure uses ALL(Features), which ignores any filters applied (including AreaSK). To ensure the measure respects the AreaSK filter, modify it as follows:
CumulativeCheckFeatures =
VAR CurrentWeekEndSK =
CALCULATE(
MAX(DatesTable[DateSK]),
DatesTable[WeekStartingDate] = MAX(DatesTable[WeekStartingDate])
)RETURN
CALCULATE(
SUM(Features[CheckFeatures]),
FILTER(
ALLSELECTED(Features), -- Changed from ALL(Features) to ALLSELECTED(Features)
Features[CreatedDateSK] <= CurrentWeekEndSK
)
)If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!Thanks for your help. Unfortunately chage from ALL(Features) to ALLSELECTED(Features) does not help. Any other suggestions ?
- Khushidesai0109
Hiiii yzhouMC
Please try thisCumulativeCheckFeatures =
VAR CurrentWeekEndSK =
CALCULATE(
MAX(DatesTable[DateSK]),
DatesTable[WeekStartingDate] = MAX(DatesTable[WeekStartingDate])
)RETURN
CALCULATE(
SUM(Features[CheckFeatures]),
FILTER(
ALLSELECTED(DatesTable),
DatesTable[DateSK] <= CurrentWeekEndSK
),
FILTER(
ALLSELECTED(Features),
Features[CreatedDateSK] <= CurrentWeekEndSK
)
)If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
anexa o arquivo
Sorry, do you want me to attach the file ? I can post some pictures, if it helps you understand the issue.
Thanks.
Hi yzhouMC ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
v-saisrao-msftCommunity Support
Hi yzhouMC,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-saisrao-msft
Hi yzhouMC,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-saisrao-msft
Hi yzhouMC,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.
