Get attributes from the latest record of a certain group

[wernerengelen]

Hi,

I have a table which holds all the clients (DK_CLIENT), their marital state, and let's say a valid from / valid until date. I would like to show a visualisation that shows the marital state and then the number of clients next to it as a measure. But the tricky part is: I only want to show the latest marital state. People change from SINGLE to MARRIED and then back to SINGLE for example. But I only want to see their latest state.

I was already on my way of calculating the last "end date" or"until date" per person (DK_CLIENT)

VAR LatestDatePerPerson = 
    SUMMARIZE(
        CALCULATETABLE(
        'FactTable',
        REMOVEFILTERS('marital state'[marital state])
    ),
        'FactTable'[DK_CLIENT],
        "LatestState",
        MAXX(
            FILTER(
                'FactTable',
                'FactTable'[DK_CLIENT] = EARLIER('FactTable'[DK_CLIENT])
            ),
            RELATED(DimSupport[date until])
        )
    )

But I am drawing a blank to get any further from this... Or maybe I am already going wrong here...?

And this time I want to get the marital state from the latest record, but there are other attributes and the next time I want another attribute from the latest record.

[rajendraongole1 · Accepted solution]

Hi @wernerengelen - First, identify the latest Valid Until date for each client using a calculated column.

IsLatestRecord =
IF(
'FactTable'[date until] =
CALCULATE(
MAX('FactTable'[date until]),
ALLEXCEPT('FactTable', 'FactTable'[DK_CLIENT])
),
1,
0
)

create a measure to count clients based on their latest marital state

LatestMaritalStateCount =
CALCULATE(
COUNT('FactTable'[DK_CLIENT]),
FILTER(
'FactTable',
'FactTable'[IsLatestRecord] = 1
)
)

Add a table or bar chart to your Power BI report.
Use the Marital State column as the category.
Use the LatestMaritalStateCount measure as the value.

Hope this works ,please check.

[Anonymous]

Hi @wernerengelen 

First of all, thank you danextian, rajendraongole1 and Bibiano_Geraldo for your interest and responses to this thread.

Based on my research, the solutions offered by rajendraongole1 and Bibiano_Geraldo should both work. If their responses helped you solve your problem, please consider accepting their replies as solutions, it will be easier for other users experiencing the same problem to find a solution.
If they didn't help you, please provide us with sample data that completely covers your problem so that we can pinpoint it more precisely and provide a solution, and a pbix file would be great. (Please remove privacy information)

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

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

[Bibiano_Geraldo · Accepted solution]

Hi @wernerengelen ,

Based on your description, make sure that you have a measure to get the latest date:

LatestValidUntilDate = 
CALCULATE(
    MAX('FactTable'[valid until]),
    ALLEXCEPT('FactTable', 'FactTable'[DK_CLIENT])
)

Now you can create the measure to get the marital state of the latest record:

LatestMaritalState = 
VAR LatestDate = 
    CALCULATE(
        MAX('FactTable'[valid until]),
        ALLEXCEPT('FactTable', 'FactTable'[DK_CLIENT])
    )
RETURN
    CALCULATE(
        FIRSTNONBLANK('FactTable'[marital state], 1),
        'FactTable'[valid until] = LatestDate
    )

Now you can place marital state on the axis and add LatestMaritalState as a measure.

[rajendraongole1 · Accepted solution]

Hi @wernerengelen - First, identify the latest Valid Until date for each client using a calculated column.

IsLatestRecord =
IF(
'FactTable'[date until] =
CALCULATE(
MAX('FactTable'[date until]),
ALLEXCEPT('FactTable', 'FactTable'[DK_CLIENT])
),
1,
0
)

create a measure to count clients based on their latest marital state

LatestMaritalStateCount =
CALCULATE(
COUNT('FactTable'[DK_CLIENT]),
FILTER(
'FactTable',
'FactTable'[IsLatestRecord] = 1
)
)

Add a table or bar chart to your Power BI report.
Use the Marital State column as the category.
Use the LatestMaritalStateCount measure as the value.

Hope this works ,please check.

[danextian]

hi @wernerengelen 

Without a workable sample data (not an image) and the expected result from that, we would only be trying to hit a target without properly aiming at it.