cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Get First Value on Matrix

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:

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6
Super User

Hi @Dizzy
Following the sample file created by @v-yangliu-msft, 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 )
)``````

Community Support

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

Frequent Visitor

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.

Super User

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

Frequent Visitor

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.

Super User

@Dizzy
Not sure if you have seen my reply below.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors