Filtering the minimum value of a measure

[USEERTEST51151]

Hello!

I am working with a matrix that contains two variables, "month" and "type," and a calculated measure called "measure1." My goal is to filter the matrix to retain only the row(s) with the minimum value of "measure1," excluding any null values. I want to focus on the specific month and corresponding values related to this minimum measure and obtain a single row in the final output. However, the large size of the dataset (~6 million rows) is causing performance challenges.

Matrix used:

	Type
Month	A	B	C	Total
1	0.45	0.51	0.01	0.97
2	0.25	0.24	0.01	0.5
3	0.05	0.02	0.25	0.32
4	0.5	0.2	0.01	0.71
5	0	0	0	0

Desired output:

	Type
Month	A	B	C	Total
3	0.05	0.02	0.25	0.32

Can you please help me out?

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i'd like to try.  Share the download link of the PBI file.

[OktayPamuk80]

Hi,

Shortly, did you check the visual calculations introduced in february '24?

https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/

Regards,

Oktay

[USEERTEST51151]

Is not helping me that much since I am trying to get that line only.

[Anonymous · Accepted solution]

Hi @USEERTEST51151 ,

I think you can create a measure to filter your martix in visual level filter.

Sample:

Measure:

Filter = 
VAR _MIN = MINX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table',DimMonth[Month]))+0)
RETURN
IF([Measure] = _MIN,1,0)

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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

[lbendlin]

you cannot measure a measure. You need to materialize it first.