The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 !
Solved! Go to Solution.
Hi @Anonymous ,
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])
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.
Hi @Anonymous ,
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])
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.
on what granularity level?
ID (distinct) level 🤗
why are there two entries for A - 2 - US - M2 ?
Because it is possible in my dataset, I need to remove duplicates.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
121 | |
81 | |
79 |