topic Re: show date and other column values of rows with min and max date in separate cards in DAX Commands and Tips

show date and other column values of rows with min and max date in separate cards

dudeeee — Wed, 14 Sep 2022 10:53:16 GMT

Date	valueA	valueB
2022-09-1	2	9
	3	3
2022-08-23	5	6
2022-09-19	6	9
2022-09-17	8	7

Hello , I am new to power bi and been stuck with this problem for a while. From above sample table I want to get the values of date, valueA and valueB for maximum and minimum date excluding the empty date.

I want to display the data from two rows in separate card visuals. I want it to work after application of other filters/slicers as well.

Re: show date and other column values of rows with min and max date in separate cards

tamerj1 — Wed, 14 Sep 2022 11:54:29 GMT

Hi dudeeee

Are Value A and Value B measures or columns in the source data table?

Re: show date and other column values of rows with min and max date in separate cards

dudeeee — Wed, 14 Sep 2022 12:18:01 GMT

tamerj1

valueA and valueB are columns from the source table

Re: show date and other column values of rows with min and max date in separate cards

tamerj1 — Wed, 14 Sep 2022 12:31:58 GMT

dudeeee
Here is same but fixed some errors

Card A Max = VAR T1 = ADDCOLUMNS ( FILTER ( VALUES ( 'Table'[Date] ), 'Table'[Date] <> BLANK () ), "@Value", CALCULATE ( SUM ( [Value A] ) ) ) VAR T2 = TOPN ( 1, T1, [@Value], DESC ) RETURN CONCATENATEX ( T2, [Date] & UNICHAR ( 10 ) & [@Value], UNICHAR ( 10 ), [Date], ASC )

Card A Min = VAR T1 = ADDCOLUMNS ( FILTER ( VALUES ( 'Table'[Date] ), 'Table'[Date] <> BLANK () ), "@Value", CALCULATE ( SUM ( [Value A] ) ) ) VAR T2 = TOPN ( 1, T1, [@Value], ASC) RETURN CONCATENATEX ( T2, [Date] & UNICHAR ( 10 ) & [@Value], UNICHAR ( 10 ), [Date], ASC )

Re: show date and other column values of rows with min and max date in separate cards

dudeeee — Thu, 15 Sep 2022 03:47:24 GMT

hello, tamerj1 I think I am near to the solution.

Is it possible to get only the value but not its sum returned.

Date	valueA	valueB
2022-09-1	2	9
	3	3
2022-08-23	5	6
2022-09-19	6	9
2022-09-17	8	7

I just want to get result as:

Min Max

2022-08-23 2022-09-19

5 6

6 9

And show them in separate cards.

Re: show date and other column values of rows with min and max date in separate cards

tamerj1 — Thu, 15 Sep 2022 04:09:01 GMT

dudeeee

Please try

Card A Max =
VAR T1 =
ADDCOLUMNS (
FILTER ( VALUES ( 'Table'[Date] ), 'Table'[Date] <> BLANK () ),
"@Value", CALCULATE ( SUM ( [Value A] ) )
)
VAR T2 =
TOPN ( 1, T1, [@Value], DESC )
VAR MaxValueDate =
MAXX ( T2, 'Table'[Date] )
VAR T3 =
FILTER ( 'Table', 'Table'[Date] = MaxValueDate )
RETURN
CONCATENATEX ( T3, [Date] & UNICHAR ( 10 ) & [@Value], UNICHAR ( 10 ) )

Re: show date and other column values of rows with min and max date in separate cards

tamerj1 — Thu, 15 Sep 2022 04:10:49 GMT

dudeeee

Please try