Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

latest value text

I am trying to create the below table;

 

i have the following data;

 

IdUsernameProduct IDProduct LevelDate

1John1High06 February 2020
2John1Low07 February 2020
3John2High25 February 2020
4Steve3Low18 February 2020
5Steve3High10 February 2020
6Steve4Low07 February 2020
7Steve4High06 February 2020
8Steve4None04 February 2020
9Steve5High24 February 2020
10Steve6Low

07 February 2020

 

So what the table is showing is based on the Max date i want to count the number of Product Ids per product level. So for example for Product ID 1 we have 2 different levels and different dates but i want to show in a chart just the latest date level for that specific product.

 

I am able to do this via calculated columns by saying whats the max date if the date equals the max date then product level but the issue with that is its not dynamic so woud not recalculate when i change date

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    You can create two measures as below, then create a bar chart with Product Level field on Axis fields and new measure “CountofPlevel" on Values field:

    NProduct Level =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Product ID] = MAX ( 'Table'[Product ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Product Level] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Product ID] = MAX ( 'Table'[Product ID] )
                && 'Table'[Date] = _maxdate
        )
    )
    CountofPlevel =
VAR _tab =
    SUMMARIZE ( 'Table', 'Table'[Product ID], "Npl", [NProduct Level] )
RETURN
    COUNTROWS ( FILTER ( _tab, [Npl] = MAX ( 'Table'[Product Level] ) ) )

    Best Regards

    Rena

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try this measure

    lastnonblank(Table[LevelDate],max(Table[Product ID]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak thank you but tht wouldnt work as i need to count the number of product ids per  level this wouldnt allow me to do that

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , Try

        countx(filter(summarize(Table,table[user], table[product],table[Product Level],"_1", calculate(lastnonblankvalue(table[LevelDate],max(Table[Product Leve])),allexcept(Table,table[user], table[product]))) ,[_1] =[Product Level]),[product])