latest value text

[Anonymous]

I am trying to create the below table;

i have the following data;

IdUsernameProduct IDProduct LevelDate

1	John	1	High	06 February 2020
2	John	1	Low	07 February 2020
3	John	2	High	25 February 2020
4	Steve	3	Low	18 February 2020
5	Steve	3	High	10 February 2020
6	Steve	4	Low	07 February 2020
7	Steve	4	High	06 February 2020
8	Steve	4	None	04 February 2020
9	Steve	5	High	24 February 2020
10	Steve	6	Low	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

[Ashish_Mathur]

Hi,

Please show the result in a simple table with an explanation.

[amitchandak]

@Anonymous , Try this measure

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

[Anonymous]

@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]

@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])

[Anonymous]

@amitchandak thank you for this am i able to do a distinct count of this as we have multiple products Ids that are the same so only want to look at them onces as looking at that i have some 2's when doing a count against the Outlet IDs

[v-yiruan-msft · Accepted solution]

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