Return the latest value by date and condition

[bv2023]

Hello,

I would like to have a PowerBI Card or Multi-row Card show the latest value from a table based on time and other conditions.

I have the following table from which I will be pulling information:

strname	datstamp	fltvalue
C1B	7/27/2023 0:01	1
C1B	7/27/2023 0:02	5
C1B	7/27/2023 0:03	6
C1B	7/27/2023 0:04	4
C1B	7/27/2023 0:05	2
C2B	7/27/2023 0:01	1
C2B	7/27/2023 0:02	4
C2B	7/27/2023 0:03	5
C2B	7/27/2023 0:04	9
C2B	7/27/2023 0:05	2

I am having trouble coming up with a measure that will return fltvalue where the strname=C1B and the most recent date. Could someone help me with the DAX code that creates a measure that will pull the most recent ftvalue for just C1B?

[bv2023 · Accepted solution]

LATEST=

VAR last_value = MAX(Data[datstamp])

VAR tag = "C2A"

Return

MAXX(FILTER(ALL(Data),Data[datstamp]=last_value &&

Data[strname]=tag),

Data[fltvalue])

[bv2023]

In words, the code needs to look at the table column [datstamp] where the date is maximum, ensure [strname]= 'C1B' and pull the value at [fltvalue]. Can someone else assist with this challenge? I do not know the syntax well enough.

[Arul]

@bv2023 ,

Can you try this measure?

Max value Per Strname = 
CALCULATE (
    MAX ( 'Table'[fltvalue] ),
    INDEX (
        1,
        ALL ( 'Table'[datstamp], 'Table'[strname] ),
        ORDERBY ( 'Table'[datstamp], DESC ),
        DEFAULT,
        PARTITIONBY ( 'Table'[strname] )
    )
)

[bv2023]

The measure compiles but does not return ftvalue of 2 when inserted into a Card in PowerBI. How do I specify or filter to show only strname=C1B? 

Thanks for your help Arul!

[Arul]

@bv2023 ,

added one filter from strname, Can you try now?

Max value Per Strname = 
CALCULATE (
    MAX ( 'Table'[fltvalue] ),
    INDEX (
        1,
        ALL ( 'Table'[datstamp], 'Table'[strname] ),
        ORDERBY ( 'Table'[datstamp], DESC ),
        DEFAULT,
        PARTITIONBY ( 'Table'[strname] )
    ),'Table'[strname] = "C1B"
)

[bv2023]

I am getting "Can't display the visual." due to 

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

Any workaround for this? 

[Arul]

@bv2023 ,

Check out these threads,

https://community.fabric.microsoft.com/t5/Desktop/The-resultset-of-a-query-to-external-data-source-h...

https://community.fabric.microsoft.com/t5/Service/The-resultset-of-a-query-to-external-data-source-h...