topic Re: DAX measure to filter only one row with lowest number per customer in DAX Commands and Tips

DAX measure to filter only one row with lowest number per customer

kokos88 — Fri, 11 Jul 2025 20:46:47 GMT

Hi,

I have such dataset:

Customer	Reason	Amount	Row Number
1	A	7	1
1	B	15	2
1	C	35	3
2	B	13	1
2	C	9	2
2	D	22	3
3	C	1	1
3	A	24	2
3	B	17	3

I have added filter: "Reason" to the report (values are: A, B, C, D).

I need measure which calculates SUM of Amount column for all customers, but it should only take one row per customer - with the lowest number of "Row Number".

Let's imagine: I have choosen on "Reason" slicer: B and C. So my measure should take only (values in orange in above table):

For Customer 1, Amount: 15 - because the lowest Row Number is 2 - for "Reason" B,

For Customer 2, Amount: 13 - because the lowest Row Number is 1 - for "Reason" B,

For Customer 3, Amount: 1 - because the lowest Row Number is 1 - for "Reason" C

So total value of this measure is 15 + 13 + 1 = 29

Can anybody help me with this? Thank you!

Re: DAX measure to filter only one row with lowest number per customer

techies — Sat, 12 Jul 2025 07:18:53 GMT

Hi kokos88 please try this

Sum_Lowest_RowPerCustomer =

SUMX(

VALUES(DATAS[Customer]),

VAR CustomerID = DATAS[Customer]

VAR MinRow =

CALCULATE(

MIN(DATAS[Row Number]),

DATAS[Customer] = CustomerID

)

RETURN

CALCULATE(

SUM(DATAS[Amount]),

DATAS[Customer] = CustomerID,

DATAS[Row Number] = MinRow

)

Re: DAX measure to filter only one row with lowest number per customer

rohit1991 — Sun, 20 Jul 2025 15:58:21 GMT

Hi kokos88 ,
Heres a DAX measure that will return the sum of Amount per customer, only for the row with the lowest "Row Number" (after applying any Reason filter):

Sum of Amount - Lowest Row per Customer = SUMX( VALUES('Table'[Customer]), VAR MinRow = CALCULATE( MIN('Table'[Row Number]) ) RETURN CALCULATE( SELECTEDVALUE('Table'[Amount]), 'Table'[Row Number] = MinRow ) )

Replace 'Table' with your actual table name.

Re: DAX measure to filter only one row with lowest number per customer

kokos88 — Mon, 14 Jul 2025 10:41:28 GMT

Thank you for you solution. It works 🙂

I have one more question... when I create table with customer and reason - sum of values from all rows is different than the measure you created.
Any idea what filter I can add to the visual to only show one row for customer? So, to show only rows highlighted in green?

Re: DAX measure to filter only one row with lowest number per customer

kokos88 — Mon, 14 Jul 2025 10:41:56 GMT

Thank you for you solution. It works 🙂

Re: DAX measure to filter only one row with lowest number per customer

v-achippa — Thu, 17 Jul 2025 07:41:51 GMT

Hi kokos88,

Thank you for reaching out to Microsoft Fabric Community.

Thank you rohit1991 and techies for the prompt response.

To also make the table visual to show only one row per customer, create a helper measure that filters out all other rows like below:

Show Lowest Row Only =

VAR MinRow =

CALCULATE(

MIN('Table'[Row Number]),

ALLEXCEPT('Table', 'Table'[Customer]),

KEEPFILTERS('Table'[Reason])

)

RETURN

IF('Table'[Row Number] = MinRow, 1, 0)

Add this measure to your table and apply a visual level filter like Show Lowest Row Only is 1

Now the table shows only the row with the lowest Row Number for each customer and the sum will match the measure result like the green rows which are highlighted.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thanks and regards,

Anjan Kumar Chippa

Re: DAX measure to filter only one row with lowest number per customer

kokos88 — Thu, 17 Jul 2025 09:21:39 GMT

Hi,

Thank you! When I try to add this measure there is error message:

A single value for column 'Row Number' in table 'Table' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Re: DAX measure to filter only one row with lowest number per customer

rohit1991 — Sun, 20 Jul 2025 15:57:52 GMT

Hi kokos88

To always show only the row with the lowest Row Number per customer (after Reason slicer/filter), add this measure to your table:

Show Lowest Row Only = VAR ThisCustomer = 'Table'[Customer] VAR MinRow = CALCULATE( MIN('Table'[Row Number]), FILTER( ALLSELECTED('Table'), 'Table'[Customer] = ThisCustomer ) ) RETURN IF('Table'[Row Number] = MinRow, 1, 0)

Then, add this measure to your table visual and set a visual-level filter to Show Lowest Row Only = 1. This should update correctly with your Reason slicer and always keep only the lowest row per customer.

Re: DAX measure to filter only one row with lowest number per customer

kokos88 — Fri, 18 Jul 2025 07:56:24 GMT

Thank you for your response!

Unfortunately the solution doesn't work...

When I did as you proposed the table shows different value than expected - one row is missing.

I have selected in slicer Reason B and C

There should be additional 1 row (highlighted in green). But column isLowestRow is 0 for this case, that's why it is not present

Re: DAX measure to filter only one row with lowest number per customer

v-kathullac — Tue, 22 Jul 2025 11:55:30 GMT

Hi kokos88 ,

Thank you for reaching out to Microsoft Fabric Community.

I hope the below screenshot will solve your issue .

Attaching the pbix file for your refrence let me know if you need any further assistance.

Thanks.

Re: DAX measure to filter only one row with lowest number per customer

v-kathullac — Mon, 28 Jul 2025 15:39:19 GMT

Hi kokos88 ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Thanks.

Re: DAX measure to filter only one row with lowest number per customer

v-achippa — Thu, 31 Jul 2025 13:18:17 GMT

Hi kokos88,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

Re: DAX measure to filter only one row with lowest number per customer

kokos88 — Thu, 31 Jul 2025 13:28:38 GMT

Thank you for your help! It worked