How to create a MEASURE that: SUMMARIZE DISTINCT, ignore slicer and a fixed FILTERING of the data

[Ocelote]

Dears,

Looking for a hint on how to achieve the creation of a measure (to be used in other calculation) as I will try to describe below: 

INPUT: I want to only work on the bold part of the below table, basically date A and only 3M ahead.

GP	ID	Period	M1	M2	M3	M4	M5	M6
EU	1	A	10	10	10	10	10	10
US	2	A		1	1	1	1	1
EU	3	A				5	5	5
US	2	A	1	1
EU	4	A	8	8	8
EU	1	B		10	10	10	10	10
US	2	B		1	1	1	1	1
EU	3	B				5	5	5
US	4	B		12	12	12

INTERMEDIATE TABLE

GP	ID	Value
EU	1	10
EU	4	8
US	2	1

My goal is to filter out the data I do not need (Period B & Date > M3), Group by distinct ID and sum the result by group. One ID has always the same value but can appear in different moment in time.

Also, it needs to ignore the slicer so that even if I select M4, it will display the fixed value (as initial range should be fixed)

OUTPUT:

GP	Sum(Value)
EU	18
US	1

I tried a lot of methods to get it into a measure. It only works when I make a separate table with the below code but then I cannot use the output for another calculation:

MEASURE = SUMX(SUMMARIZE(filter(table, table[Date]=A && table[M]<[M3]  ),table[GP],table[Value]),'table'[Value])

Thank you for your help !

[v-yuaj-msft · Accepted solution]

Hi @Ocelote ,

Based on your description, you can create a measure as follows.

Measure = 

var x1=SUMMARIZE(FILTER('Table',[Period]="A"),'Table'[GP],'Table'[ID],'Table'[M1],'Table'[M2],'Table'[M3])

var x2=FILTER(SUMMARIZE(x1,'Table'[GP],'Table'[ID],"Value",MAX('Table'[M1])),[Value]<>BLANK())

return

SUMX(x2,[Value])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

on what granularity level?

[Ocelote]

ID (distinct) level 🤗

[lbendlin]

why are there two entries for A - 2 - US - M2 ?

[Ocelote]

Because it is possible in my dataset, I need to remove duplicates.