Measure not acting as it should in another formula

[Anonymous]

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?????

[v-kelly-msft]

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'))

If not,provide some sample data about year_period and show me your logic about the max month,I will give you some advice.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

@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....

I really appeciate your help!

Jordan

[v-kelly-msft]

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

Did I answer your question? Mark my reply as a solution!

[Anonymous]

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

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ，

Using "Split columns by positions"in query editor:

reference:https://docs.microsoft.com/en-us/power-query/split-columns-positions

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

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

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

@v-kelly-msft 

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

[v-kelly-msft]

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

Did I answer your question? Mark my reply as a solution!

[smpa01]

@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/

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic