cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum of Max at row level in a measure with filters

Hi,

I want to be able to create a calculation at row level in a measure without adding a calculated column. In my Patient data, I want to calculate the sum of patients for each month but before calculating sum, I want to apply a condition at row level which is Max(Patients, 0).

I am able to achieve this using the following measure -

test - Max at row level = CALCULATE(SUMX(Patients,MAX(Patients[Patients],0)))

But I want to able to apply filters like Type = "Paid" and Product = "Product A" and I am not sure how to that in the above mesure so that it keeps the calculation intact.

Please let me know if you have any solutions. The client is not keen on adding a column in the data and wants to calculate everything in a measure.

Thanks!
1 ACCEPTED SOLUTION
Resolver II

@Pdawar Sorry yes that will not work.

For having that flexibility to use filter from dimension row wise. You can try using CALCULATETABLE DAX function.

Max at row level  =
var _filteredPatientstable = CALCULATETABLE(Patients ,Patients[TYPE] = "Paid", product[Product]="Product A" )

return SUMX(_filteredPatientstable, MAX(Patients[Patients] )

I think this can work for your use case.

6 REPLIES 6
Frequent Visitor

@saurabhtd thank you! Yes this works.

Frequent Visitor

I tried this logic to filter but this doesn't work because the first argument of filter function is to provide the table name and in this case we are not providing a table for Product column.

Resolver II

@Pdawar Sorry yes that will not work.

For having that flexibility to use filter from dimension row wise. You can try using CALCULATETABLE DAX function.

Max at row level  =
var _filteredPatientstable = CALCULATETABLE(Patients ,Patients[TYPE] = "Paid", product[Product]="Product A" )

return SUMX(_filteredPatientstable, MAX(Patients[Patients] )

I think this can work for your use case.

Resolver II

@Pdawar  You can apply those filter inside FILTER DAX Fuction and then apply the SUMX on that filtered table. You can try the below formula.

Max
at row level  =
var _filteredPatientstable = FILTER(Patients,Patients[TYPE]="Paid" && Patients[Product]="Product A")
return
SUMX(_filteredPatientstable, MAX(Patients[Patients] )

Frequent Visitor

Thank you! And can filter from another table can also be applied? So the data model is star schema and the product filter needs to be applied from the Product table.

Resolver II

@Pdawar If the data model is star schema then you can apply the filter condition in FILER DAX function from Product dimension. But there needs to be relationship between fact and dimesion.
for example :
Max at row level  =
var _filteredPatientstable = FILTER(Patients,Patients[TYPE]="Paid" && product[Product]="Product A")
return
SUMX(_filteredPatientstable, MAX(Patients[Patients] )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors