Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count for Card Viz

Hi everyone!

 

I'm looking to get the count for ID that has a certain category I want, for the latest date I want. Also, I only want it to count the last way category if I filter, for example, A where the latest date could be different months. Eg:

 

IDLast Date UpdatedCategory
21/1/20A
21/2/20A
21/4/20B
51/4/20B
71/7/21A
91/7/21A

 

So for the count result for category B, I would be looking for: 2

And for A would be: 2

 

Thank you all so much in advance for sharing your expert advice!

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi @

    You can try this.

    Create the measure:

    vcount result =
VAR _selCategory =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Last Date Updated] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            'Table'[Last Date Updated] = _maxdate
        )
    )

    Result:

     

    See sample file attached below.

     

    Best Regards,

    Community Support Team _ Tang

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a measure like

     

    Measure =
    VAR __id = MAX ('Table'[ID] )
    VAR __date = CALCULATE ( MAX('Table'[Last Date ] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
    CALCULATE ( count ('Table'[Category] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Last Date ] = __date )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Thank you so much for your kind response and amazing insights!

       

      Unfortunately, I tried your code but it didn't work. I also tried adding a "RETURN" before the CALCULATE function however this didn't work either. I was wondering if you possibly have any other ideas?

       

      Thank you!

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi @

    You can try this.

    Create the measure:

    vcount result =
VAR _selCategory =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Last Date Updated] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            'Table'[Last Date Updated] = _maxdate
        )
    )

    Result:

     

    See sample file attached below.

     

    Best Regards,

    Community Support Team _ Tang

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