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
Andreas_H
Frequent Visitor

CALCULATE and MAX: Problem using result in another Measure

Dear Power BI enthusiasts,

 

my name is Andreas and I am totally new to this community. I just started to use Power BI Desktop and made so far good progress with video tutorials and hands on practice. I want to work with monthly actual cost data and therefore also need to determine which is the highest month of the current year that carries costs. In my testfile this is march, so numeric a 3. But since I do have also 2016 costs I firstly need to determine the maximum year with actuals. This I found out this evening:

works: MaxYear = CALCULATE(MAX(ds[Year]);filter(ds;[Act]<>0))

 

This formula returns 2017 which is correct. In a second step I want to determine the maximum month in 2017 that carries cost and used:

does not work: MaxMonth = CALCULATE(MAX(ds[Month]);filter(ds;[Year]=[MaxYear]);filter(ds;[Act]<>0))

 

So I used the result 'MaxYear' in a second measure. But it does not work! If I hardcode the year as 2017 instead of [MaxYear] it DOES work!

works: MaxMonth = CALCULATE(max(ds[Month]);filter(ds;[Year]=2017);filter(ds;[Act]<>0))

 

What is the reason for this seldom behaviour? Any ideas? I am nearly desperate tonight, because I made such good progress and do fail now without a clue why.

And one more question: How many 'filter' parameters can be combined in the 'calculate' formula?

 

Many thanks in advance for your attention and helpful answers!

 

Best regards, Andreas

1 ACCEPTED SOLUTION

@Andreas_H

 

Hi, that's great that you have a little a piece of my country with you.

 

I have a alternative to solve your question

Create a measure :

 

MaxMonth =
VAR Maxyear =
    CALCULATE (
        MAX ( AndreasTable[Year] ),
        FILTER ( VALUES ( AndreasTable ), CALCULATE ( SUM ( AndreasTable[Act] ) <> 0 ) )
    )
RETURN
    CALCULATE (
        MAX ( AndreasTable[Month] ),
        FILTER ( AndreasTable, AndreasTable[Year] = Maxyear && AndreasTable[Act] <> 0 )
    )

Max.png

 




Lima - Peru

View solution in original post

7 REPLIES 7
Vvelarde
Community Champion
Community Champion

@Andreas_H

 

hi buddy, please try with this DAX Measure

 

MaxMonth =
CALCULATE (
    MAX ( DS[Month] ),
    FILTER (
        DS,
        DS[Year] = MAX ( DS[Year] )
            && DS[Act] <> 0
    )
)



Lima - Peru

Dear Vvelarde,

 

unfortunately this does not work. It returns nothing. But I assume the line 'MAX ( DS[Year] )' should be 'MAX ( DS[MaxYear] )' ? If I correct it to this it is not acceptex by Power BI.

 

Any other Ideas?

 

Thank you & Best regards, Andreas

@Andreas_H

 

Hi, to get a correct answer maybe you need to specify the structure of your table.

 

In test environment i create something like this:

 

TableLook.png

Max.png

 

If you have a different structure, please post sample data or a link to test PBIX.

 

 

 




Lima - Peru

Dear Vvelarde,

 

you're right. I need to make sure we are talking about the same structure. So I did load your data into Power BI and it worked. Then I compared it to my data and looked for differences. The difference is that I also do have the year 2018 with 0 in the [Act] column, but they are there because in 2018 I might have a forecast. So I added similar 2018 rows with a valid [Month] entry, but no [Act] entry and then the DAX formula did not calculate anymore.

 

YearActMonth
2016-11
2016-22
2016543
2016544
2016545
2016-341634,66
2016547
2016548
2017-215241
2017542
201703
2017-54
2017-5,1235
201706
201707
201801
201802
201803
201804

 

Maybe I did not explain it correctly in the first instance... So I am looking for the MAX year that has Actuals <> 0 and within this year the MAX Month.

 

I tested it back and forth. As soon as 2018 is in, your formula refuses. Otherwise it works perfect! But why?

 

I am so grateful that you read my post and that now the difference is figured out. But for today I am exhausted. I am from Berlin and here it is 11 p.m. I would be so grateful If you could invest once more some of your experience to solve this. The difference in the formulas will show me a lot with regards to the logic of DAX.

 

Thank you very much! Best regards from Berlin, Andreas

 

PS: You are from Lima? I was 2014 in Peru and bought a ring in Cusco handcrafted out of a Soles coin. Whenever I wear it, it reminds of one of my best times ever... Smiley Happy

 

Have a good night later on!

@Andreas_H

 

Hi, that's great that you have a little a piece of my country with you.

 

I have a alternative to solve your question

Create a measure :

 

MaxMonth =
VAR Maxyear =
    CALCULATE (
        MAX ( AndreasTable[Year] ),
        FILTER ( VALUES ( AndreasTable ), CALCULATE ( SUM ( AndreasTable[Act] ) <> 0 ) )
    )
RETURN
    CALCULATE (
        MAX ( AndreasTable[Month] ),
        FILTER ( AndreasTable, AndreasTable[Year] = Maxyear && AndreasTable[Act] <> 0 )
    )

Max.png

 




Lima - Peru

Dear Vvelarde,

 

It works! It works! It works! Yes! Thank you so much... I need to investigate more on DAX commands tomorrow. I did not expect it would be so complicated in the end... But you rocked it!

 

I would like to buy you a 'Cusquena Roja', which was my favourite...

 

Thank you once more & Have a good night!

 

Best regards, Andreas

Hi @Andreas_H,

Congratulations, you have resolved your issue, pleas mark the right reply as answer, so that more people will find the workaround easily.

Thanks,
Angelia

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.