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

Helper I

## using MAX to identify the max value calculated by a measure

I'm trying to use the MAX funtion to reference a measure, but getting an error stating that MAX only references a column.  what is the best way to get around this to where it will allow me to reference a measure?

22 REPLIES 22
Regular Visitor

One solution is to create a calculated table within your measure and use MAXX. You create a table within your measure using SELECTCOLUMNS, add a calculated column with your measure, and take the maximum value of the calculated column.

``MaxMeasure = MAXX(SELECTCOLUMNS('Table',"Column",'Table'[Column1],"Measure",[Measure]),[Measure])``

Depending on how your model is set up, you may need to make changes to your calculated table to account for filters.

Example:
Simple table:

 Column1 red yellow blue

And a measure that assigns a value:

``Measure = SWITCH(SELECTEDVALUE('Table'[Column1]),"blue",1,"red",2,"yellow",3)``

New measure to find the max of my first measure:

``MaxMeasure = MAXX(SELECTCOLUMNS(ALLSELECTED('Table'),"Column",'Table'[Column1],"Measure",[Measure]),[Measure])``

Result:

Responsive Resident

The MAX function can only be used to reference a column, not a measure. However, there is a workaround for this. You can create a new calculated column in your table that references your measure and then use the MAX function to reference the calculated column.

To create a calculated column, go to the Modeling tab in the Power BI Desktop ribbon and click on "New Column." In the formula bar, enter the formula for your measure. For example, if your measure is called "Total Sales," the formula would be "= [Total Sales]". Press enter to create the calculated column.

Once the calculated column is created, you can use the MAX function to reference it in your visualization. For example, if your calculated column is called "Total Sales Column," the formula for your MAX function would be "MAX('Table'[Total Sales Column])".

This should allow you to reference your measure using the MAX function in Power BI.

Helper II

Thank you for your response. However a calculated column is unfortuneatly not working since the MAX date per customer is dyanmic to the selected dates in the slicer.

Anonymous
Not applicable

Measures only come up with a single result, which is why the Max having trouble. What Max wants to do is scan through a list of values and give you the highest.

Instead you need to write a DAX expression that will calculate a set of values, based on your original measure, and then find the max of that set.

Here is something quickly that might work:

```Measure Max = IF(
countrows(values('Table'[GroupingField])) = 1,
[YourMeasure],
MAXX(
values('Table'[GroupingField]),
[YourMeasure]
)
)```

Where grouping field is what thing you want to group your measure results by.  For example, if you wanted to calculate your measure on monthly figures, you'd need a field that denotes the YearMonth.  If it was daily, you could use a single date here.

New Member

Perfect solution for me after a large research, thanks a lot, love you bro ...

Helper II

Hi,

I am trying to use this DAX measure as well.

IF(
countrows(
values('table'[ID_CUSTOMER_GUID]))=1;
[LastDate12-6Month];
MAXX(
values(
'table'[ID_CUSTOMER_GUID]);
[LastDate12-6Month]
)
)

However, as you can see sometimes it is working and sometimes not. Do you have any idea why?

Super User

Hi,

What exactly are you trying to do?  Explain the question, show the expected result and share the download link of the PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi,

I'am trying to count only the last visit for our customers with a datesinperiod of 12-6 months ago.
As you can see, it does only count the last visit of 0250E5CF-313F-496E-B1FD-2CD7450B6EF6 for the period of 12 to 6 months ago. However it does not count the last visit of 0272324A-992B-46F0-9E89-D2E3AB720AF7. I am not getting why. Both clients does have visitdates after the selected period, so this does not cause the difference.

Super User

Hi,

In the image that you have shared, you already have the answer in the second column.  Why are you writing yet another measure?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi,

It is not counting every customer. As you can see, it does not count 0272324A-992B-46F0-9E89-D2E3AB720AF7 in the second column.

Super User

Hi,

The LastDate12-6Month measure is working perfectly as seen in the image.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi,
Yeah but this is giving me multiple dates if I unfold the different dates.
I am trying to count the last visit date per customer. However, If I try to with the following measure:

MaxDate/Customer12-6Month =
IF([LastDate12-6Month];"terugkerend";BLANK())

VAR vRows = VALUES('table'[CustomerID])
RETURN
COUNTX(FILTER(vRows;[MaxDate/Customer12-6Month]="terugkerend");1)

It counts all dates, not only the lastest date per customer.
Super User

I am still not clear of what youw want.  Share the download link of the PBI file and show the expected result there.  If possible, please ensure that the headings are in English.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

This is great - really worked for me. Thank you!

Helper I

Could't quite get it to work.  Here's a sample data set.

Revenues (measure)      # of Machines (measure)    Rev/machine (measure)      MAX Rev/machine     Opportunity

Customer A           35,000                         5                                             7,000                           7,000                           0

Customer B           60,000                         15                                           4,000                           7,000                           45,000

Customer C           25,000                         10                                           2,500                           7,000                           45,000

what I'm trying to do is write a DAX equation that will show the MAX (7,000) across all customers which will then allow me to calculate the opportunity across the not-MAX customers (A & B).  The opportunity will be calculated by subtracting the MAX rev/machine from the rev/machine and multiplying that by the number of machines.

Example:  Considering customer B, the opportunity will be 7,000 (MAX rev/machine) - 4,000 (rev/machine) x 15 (machines) = 45,000

Anonymous
Not applicable

Ok lets try these two measures:

```Rev/Machine Max = Calculate(
IF(
countrows(values('TableName'[Customer])) = 1,
[Rev/Machine],
MAXX(
values('Table'[Customer]),
[Rev/Machine]
)
),
ALL('TableName')
)```

`Opportunity = [Rev/Machine Max] - [Rev/Machine] * [# of Machines]`
Helper I

I started breaking down the expression you provided trying to figure out what part is not working.

TEST1 - =countrows(values('TableName'[Customer])) = 1       WORKED

TEST2 - =calculate(if(countrows(values('TableName'[Customer])) = 1,[rev/maching], "you"))           DID NOT WORK.

If I replace [rev/machine] with other values under quotes, it'll work.  I tried inserting different measures but calculation failed.  Definitely something off when having to reference a measure.

TEST3 - =calculate(if(countrows(values('TableName'[Customer])) = 1, "me","you"))          WORKED

I'll be doing more testing to see if I can get this to work.

Anonymous
Not applicable

Thats because you can't use a measure within a calculate statement.  You will need to take all of the code inside that measure, and put it inside your calculate statement.  If your measure references other measures, you'll need to get those bits of code too.  Dax lets you spread out your code (like a programming language) to keep it all manageable.

Helper I

After reading the Power Pivot and Power BI book by Rob Collie & Avi Singh, I figured this thing out.

1.  =MAXX(VALUES(vw_FST_Revenue_Paid_Per_Production_Tool_Report[Parent Customer]),[RpT PARTS])

This allowed for me to get the MAX revenue per tool across the specific platforms.  The MAX was noted only on the subcategory total.

2.  =MAXX(ALLSELECTED(vw_FST_Revenue_Paid_Per_Production_Tool_Report[Parent Customer]),[RpT PARTS])

This allowed me to filter out certain customers while adjusting the revenue per tool Max based on what i included/excluded.  Similar to "1." above, but this DAX noted the MAX across all rows, not just the subcategory total.

3. I then subtracted those two and multiplied it by the tool counts.  The result was the opportunity.

Helper I

We're getting closer.  the formula was accepted and is calculating something as there is a value noted even though it's not the correct MAX i'm looking for.  If I can figure what is being calculated to reflect the value shown, I think i'll be able to close this out.

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.