Question of Filter the Measure

yzhouMC · ‎03-20-2025

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.

v-shex-msft · ‎03-24-2025

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

HugoVeras · ‎03-21-2025

anexa o arquivo

yzhouMC · ‎03-21-2025

Sorry, do you want me to attach the file ? I can post some pictures, if it helps you understand the issue.

Thanks.

Khushidesai0109 · ‎03-20-2025

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!

yzhouMC · ‎03-21-2025

Thanks for your help. Unfortunately chage from ALL(Features) to ALLSELECTED(Features) does not help. Any other suggestions ?

Khushidesai0109 · ‎03-23-2025

Hiiii @yzhouMC

Please try this

CumulativeCheckFeatures =
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!