topic Get First Value on Matrix in DAX Commands and Tips

Get First Value on Matrix

Dizzy — Tue, 16 Apr 2024 09:55:44 GMT

Dear all,

I'm currenty working on issue to get only first value on Marix within Month, I used Firstnonblankvalue but I get the result without month, as you can see below I want to get only the yellow values:

Best regrads

Re: Get First Value on Matrix

tamerj1 — Tue, 16 Apr 2024 13:42:41 GMT

Hi Dizzy
Please elaborate on your expected result perhaps on top of the example that you've provided.

Re: Get First Value on Matrix

Anonymous — Wed, 17 Apr 2024 06:35:56 GMT

Thanks for the reply from tamerj1 , please allow me to provide another insight:

Hi Dizzy ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure = var _date= MINX( FILTER(ALL('Table'), 'Table'[Account Number Name CRM]=MAX('Table'[Account Number Name CRM])&& 'Table'[Month_Year]=MAX('Table'[Month_Year])&& 'Table'[Value]<>BLANK()),[Date]) return SUMX( FILTER(ALL('Table'), 'Table'[Account Number Name CRM]=MAX('Table'[Account Number Name CRM])&& 'Table'[Month_Year]=MAX('Table'[Month_Year])&& 'Table'[Date]=_date),[Value])

2. Result:

Best Regards,

Liu Yang

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

Re: Get First Value on Matrix

Dizzy — Thu, 25 Apr 2024 11:18:11 GMT

Thank you.

This is not why I'm locking for.

In your example, it schould be only Feb 2024 for A and C Only Jan.

Ony first value sorted by Month.

Re: Get First Value on Matrix

tamerj1 — Thu, 25 Apr 2024 12:37:17 GMT

Hi Dizzy
Following the sample file created by Anonymous, please try the following

Measure = SUMX ( SUMMARIZE ( 'Table', 'Table'[Account Number Name CRM], 'Table'[Month_Year] ), VAR CurrentMonth = 'Table'[Month_Year] VAR CurrentAccountTable = FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Account Number Name CRM] ) ), 'Table'[Value] <> BLANK ( ) ) VAR FirstRecord = TOPN ( 1, CurrentAccountTable, 'Table'[Date], ASC ) VAR FirstMonth = MAXX ( FirstRecord, 'Table'[Month_Year] ) VAR FirstValue = MAXX ( FirstRecord, 'Table'[Value] ) RETURN IF ( CurrentMonth = FirstMonth, FirstValue ) )

Re: Get First Value on Matrix

Dizzy — Thu, 25 Apr 2024 12:39:09 GMT

Hi tamerj1

the value what I expected are in yellow in sorted by Fiscal Month.

The Last Row in my example is returned two values one in august and other one in january, I want to have at the end only august.

Re: Get First Value on Matrix

tamerj1 — Thu, 25 Apr 2024 14:55:06 GMT

Dizzy
Not sure if you have seen my reply below.