cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Measure not acting as it should in another formula

Ok, I have a simple measure "maxmo2" that brings back the maximum month from a set of data.  Right now, it equals 9 for September.

Looks great right?

I'm using this measure in another larger measure to count the number of lawyers with >= 500 hours.

In my example, the correct number of lawyers in 2020 is 84 and 2021 is 125.  I can get these correct results when I replace my maxmo2 measure with "9".  I don't want to have to update this number each month and unfortunately, when I use my maxmo measure in the calcuation, I get inaccurate results.

Wrong results using maxmo2 (circled below):

Correct results when I replace "maxomo2" with 9, though they should really be the same thing.  Why won't my measure that =9 not =9 in my larger measure????  Driving me crazy.  As shown in my first screenshot, maxmo = 9 right?????

1 ACCEPTED SOLUTION
Community Support

Hi  @Anonymous ，

Using "Split columns by positions"in query editor:

Or you could simply using dax,create a calculated column as below:

``maxmo2 = right(max(TKD[year_period]),2)``

Best Regards,
Kelly

9 REPLIES 9
Community Support

Hi @Anonymous ，

Based on your descriptions,one thing is for sure that your measure maxmo2 is not correct in your senario,as the output will be affected by the context.

Does your ''year _period " column like below?

"2021/10"

If so,take the suggestions from @Anonymous ,using Month can return the correct answer,and you can use below expression to give you a fixed month value returned.

``maxmo2=CALCULATE(MONTH(MAX(TKD[year_period])),ALLSELECTED('TKD'))``

Best Regards,
Kelly

Anonymous
Not applicable

@v-kelly-msft Hi Kelly,  my year_period looks like 202110.  It appears my maxmo2 measure is not working correctly due to it being "summarized" in this formula:

Count Atty>500 = COUNTX(filter(summarize(TKD,TKD[timekeeper_id],"_1",Calculate(sum(TKD[Atty Hours]), FILTER(Dates,Dates[YTD Flag]="Show YTD"))),[_1]>=(500/12*[maxmo2])), TKD[timekeeper_id])

If I could get the max mo for all selected as you mentioned, I think that would make it work.

I tried using the formula you suggested:
`maxmo2=CALCULATE(MONTH(MAX(TKD[year_period])),ALLSELECTED('TKD'))`
but that's returning 5 for some reason instead of 9, which is the max mo in the TKD table.

My maxmo2 formula is as follows:

maxmo2 = CALCULATE(value(right(max(TKD[year_period]),2)))

It works when it's not calculating differently for every line item (or person) in the data.  I still haven't been apple to get it to calculate on the aggregate level in the Count Atty >500 formula....

Jordan

Community Support

Hi @Anonymous ，

I see,I have a simple way,you may take a try,change measure "maxmo2" to a calculated column,if you need a confirmed value from maxmo2 which wont be affected by context,change it to a calculated column should be a nice choice.

Best Regards,
Kelly

Anonymous
Not applicable

Thanks @v-kelly-msft

Sorry for the handholding, but do you know how I'd go about writing that formula for the calculated column?

Here's a snapshot of my data (see year_period in 3rd column)

Thanks,

Jordan

Community Support

Hi  @Anonymous ，

Using "Split columns by positions"in query editor:

Or you could simply using dax,create a calculated column as below:

``maxmo2 = right(max(TKD[year_period]),2)``

Best Regards,
Kelly

Anonymous
Not applicable

This is the error when I try creating the calculated column.  Please see below.

It's been a while since I've worked in Power BI and I just don't remember this being so complicated!  Your help would be much appreciated!

Thanks,

Jordan

Community Support

Hi  @Anonymous ,

Right is a dax function,it should be used in an dax expression,in M query,it should be Text.Start.

Check the reference below:

https://docs.microsoft.com/en-us/powerquery-m/text-start

Best Regards,
Kelly

Super User

@Anonymous  my best guess is, SUMMARIZE is the culprit here. One of the rule of thumbs of DAX is not to use SUMMARIZE unless you fully undertsnad what it does. Can you use something else other than SUMMARIZE and see if you can turn this around. SUMMARIZE can make things go sideways unless you are really fully confident about it.

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

Hey @Anonymous ,

there are a few things that are irritating me.

First is your approach with the month number. When you just want to get the month number, you can use the MONTH function, that will return the month as number:

``MONTH( MAX( TKD[year_period] ) )``

Then your measure is unreadable in the screenshot. Please use a formatter to return properly formatted code. Just paste the code on the following website and you get nice formatted DAX code:

DAX Formatter by SQLBI

Next point, I have no idea about your data, so I don't know what you are doing in your summarize and filter functions. Please give more context or even better an example file. Then it's easier to help you.

And last point, from my feeling your approach seems to be too complicated. Do you want to filter a specific table for a specific value? Then I think there are better approaches with CALCULATE and a modification of the filter context. But for this I need more context in order to help you.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors