Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I have a table that looks like this:
Reporting Group Id | Investment Method | Date Key | Value |
1 | Internal | 20200101 | 50 |
1 | External | 20200101 | 100 |
1 | Internal | 20200201 | 100 |
1 | External | 20200201 | 20 |
1 | Internal | 20200301 | 500 |
2 | Internal | 20200101 | 500 |
2 | External | 20200101 | 200 |
2 | Internal | 20200201 | 300 |
2 | External | 20200201 | 50 |
3 | Internal | 20200101 | 400 |
3 | External | 20200101 | 200 |
3 | Internal | 20200201 | 100 |
3 | External | 20200201 | 300 |
I need to create a summerized table give me just the investment method by the reporting group for each date key based on the max value for each date. So the results should look like this:
Reporting Group Id | Investment Method | Date Key | Value |
1 | External | 20200101 | 100 |
1 | Internal | 20200201 | 100 |
1 | Internal | 20200301 | 500 |
2 | Internal | 20200101 | 500 |
2 | Internal | 20200201 | 300 |
3 | Internal | 20200101 | 400 |
3 | External | 20200201 | 300 |
I'm a little bit stuck.
Solved! Go to Solution.
Hi @GlynMThomas
You can build a summarize table to show max value for each ID and Date Key by summarize function.
Summarize Table =
SUMMARIZE (
FILTER (
'Table',
'Table'[Reporting Group Id ] = 'Table'[Reporting Group Id ]
&& 'Table'[Date Key] = 'Table'[Date Key]
&& 'Table'[Value]
= MAXX (
FILTER (
'Table',
'Table'[Reporting Group Id ] = EARLIER ( 'Table'[Reporting Group Id ] )
&& 'Table'[Date Key] = EARLIER ( 'Table'[Date Key] )
),
'Table'[Value]
)
),
'Table'[Reporting Group Id ],
'Table'[Investment Method ],
'Table'[Date Key],
'Table'[Value]
)
Result is as below.
You can download the pbix file from this link: Max group by id and date key
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @GlynMThomas
You can build a summarize table to show max value for each ID and Date Key by summarize function.
Summarize Table =
SUMMARIZE (
FILTER (
'Table',
'Table'[Reporting Group Id ] = 'Table'[Reporting Group Id ]
&& 'Table'[Date Key] = 'Table'[Date Key]
&& 'Table'[Value]
= MAXX (
FILTER (
'Table',
'Table'[Reporting Group Id ] = EARLIER ( 'Table'[Reporting Group Id ] )
&& 'Table'[Date Key] = EARLIER ( 'Table'[Date Key] )
),
'Table'[Value]
)
),
'Table'[Reporting Group Id ],
'Table'[Investment Method ],
'Table'[Date Key],
'Table'[Value]
)
Result is as below.
You can download the pbix file from this link: Max group by id and date key
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try this measure:
Max Value =
VAR vReportingGroup =
MAX ( GroupTest[Reporting Group Id] )
VAR vDateKey =
MAX ( GroupTest[Date Key] )
VAR vMaxValue =
CALCULATE (
MAX ( GroupTest[Value] ),
FILTER (
ALL ( GroupTest ),
GroupTest[Reporting Group Id] = vReportingGroup
&& GroupTest[Date Key] = vDateKey
)
)
VAR vResult =
IF ( MAX ( GroupTest[Value] ) = vMaxValue, vMaxValue, BLANK () )
RETURN
vResult
Proud to be a Super User!