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
Anonymous
Not applicable

Choose earliest value as true value

Hi,

 

I'm stuck on this annoying problem. I would like to indicate where the highest cost arises based on 

- NAME

- ALIAS

- BRANCH

- MONTH

 

Now my following MAX(COST)-column makes the following calculation:
IF(CALCULATE(MAX('TABLE'[COST]),

ALLEXCEPT('TABLE', 'TABLE'[MONTH],
'TABLE'[NAME],
'TABLE'[ALIAS],
'TABLE'[BRANCH))='TABLE'[COST],'TABLE'[COST],BLANK())

 

And my INDICATOR-column then makes it as a "1" if the entry is the maximum.

 

I have following table

IDNAMEALIASBRANCHMONTHYEARCOSTMAX(COST)INDICATOR
1VICTORVLACAR120206206201
2VICTORVLACAR12020453  
3VICTORVLACAR120206206201
4VICTORVLACAR12020249  
5VICTORVLATRUCK22020121912191
6CARLCHOCAR220203103101
7CARLCHOTRUCK22020918  
8CARLCHOTRUCK22020132813281

 

But, I only want my INDICATOR to count the earliest MAX-value, i.e. the second "620" given the same filters should just be blank as following:

 

IDNAMEALIASBRANCHMONTHYEARCOSTMAX(COST)INDICATOR
1VICTORVLACAR120206206201
2VICTORVLACAR12020453  
3VICTORVLACAR12020620  
4VICTORVLACAR12020249  
5VICTORVLATRUCK22020121912191
6CARLCHOCAR220203103101
7CARLCHOTRUCK22020918  
8CARLCHOTRUCK22020132813281

 

As you can se from the table MAX(COST) is blank for ID='3'. How do I make sure my INDICATOR takes this into account? 

 

Thanks in advance.

4 REPLIES 4
parry2k
Super User
Super User

@Anonymous here is the measure and the result

 

Max Cost = 
VAR __table =         
        ALLEXCEPT (
            'Cost', 
            'Cost'[MONTH],
            'Cost'[NAME],
            'Cost'[ALIAS],
            'Cost'[BRANCH]
        )
VAR __id = 
CALCULATE (
    MIN ( Cost[ID] ),
    TOPN ( 1, __table, CALCULATE ( MAX ( Cost[COST] ) ), DESC ) 
)
VAR __return =  
IF ( MIN ( Cost[ID] ) = __id , 1  )
RETURN __return

 

parry2k_0-1612992626759.png

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

When I try the code it just gives me the circular reference error. How can that be?

Hi @Anonymous ,


You need to copy the code to measure instead of calculated column.

v-henryk-mstf_0-1613545955360.png

v-henryk-mstf_1-1613545980765.png


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

What if I don't have an ID-column? How should I then approach it? I know it sounds silly, but making this code work without using the ID would be better in my case 🙂

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.