Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mlleverino
Helper I
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?

20 REPLIES 20
Adamboer
Responsive Resident
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.

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.

 

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? 

JolienR_0-1681330180338.png

 

 

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
https://www.linkedin.com/in/excelenthusiasts/

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. 

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
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)

JolienR_0-1681374704886.png

 

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

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
https://www.linkedin.com/in/excelenthusiasts/

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

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]

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.

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.

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.

Anonymous
Not applicable

Just realised i might have made an error with this formula.  Try this instead:

Opportunity = ([Rev/Machine Max] - [Rev/Machine]) * [# of Machines]

 

Order of operations is important lol

Thanks Ross. I did notice that one.
I'm going to play around with the first calculation to see if I can get it to work. I studied the syntax you provided to understand what was being referenced and it's starting to make sense.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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