Forum Discussion

itsmeanuj's avatar
itsmeanuj
Helper IV
3 years ago
Solved

Choose first instance based on the condition

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

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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