Choose first instance based on the condition

[itsmeanuj]

Hi,

so my data contains ~ 1M rows with more attribute columns than what i have included in the sample data. The Dummy PBIX file is placed here :  https://drive.google.com/file/d/1Yt1m0BUM483OQdsh-_aaaZJn5oQyirjX/view?usp=sharing

Now I need 2 visuals -

1st visual - Pie chart. This contains values from "Has Shipment" column. So it should show 1 and 0.
If you notice, data contains multiple instances of IDs. I need to create a measure that would pick up the latest (based on the Stamp column) value of ID & Product Combination. This means that the total on the pie chart should show the total count of unique ID&Product combinations. e.g. for Product B, the count of IDs would be 92 with the split between 1,0 ("has shipment" column)

if the latest (based on the "stamp" column) ID product combination has a value 1 for "has shipment" then, consider 1 and similarly 0 if the latest value is 0

2nd visual - this is a simple table visual with details of IDs. Note this count of rows should match the total count in 1st visual. e.g. 92 for Product B.

Please note that this count should be dynamic and adjust itself with the date filter and product filter at the top (see in the attached PBI)

Thanks,
Anuj

[itsmeanuj]

@Greg_Deckler @amitchandak - can you please help?

[Anonymous · Accepted solution]

Hi @itsmeanuj ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

Count of IDs = 
VAR _latestdate =
    CALCULATE ( MAX ( 'Data'[STAMP] ), ALLSELECTED ( 'Data' ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Data'[ID] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[HAS_SHIPMENTS] = SELECTEDVALUE ( 'Data'[HAS_SHIPMENTS] )
                && 'Data'[STAMP] = _latestdate
        )
    )

2. Create a pie chart and table visual

Best Regards