DAX with conditional filter

[Anonymous]

Hi Folks,

I am getting crazy here, i cannot just fugure this out, as you can see in the pic below, i have a table which was filter by Rig name which is 582, you can see the column "Vendor", the bottom vendor name "XTO" is the latest client, I am trying to achieve is to select the bottom or latest vendor name in the column and assign a #1 to all the vendors under that name, however it cannot be hard coded because in a month a new cliento will be at the bottom, and so i would want the measure to add 1 to all the clients with the same bottom vendor name,

I have tried so many different things:

this measure works but i had to but the name of the vendor as string:

_amount = 
VAR
    _MAXOP = CALCULATE(MAX('Well List'[Operator]))
RETURN
   if( CALCULATE(MAX('Well List'[Operator])) = "XTO", 1, "NO")

this returns this, exactly what i need:

However when trying to put it dynamically there is no filter and just returned everything again as in the first picture:

_amount = 
VAR
    _MAXOP = CALCULATE(MAX('Well List'[Operator]))
RETURN
   if( CALCULATE(MAX('Well List'[Operator])) = _MAXOP, 1, "NO")

Any idea how i can set the max value dynamically and still get the result as if it were hard coded?

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[AllisonKennedy]

I don't understand what the black chart is showing and how it relates to the problem, can you please elaborate?

Also, what are you trying to find, LATEST customer, or customer who comes last in the alphabet? They are very different DAX calculations.

Can you please provide sample data table as html or link to pbix file too so we can help better?

As a side note, I don't think you need the CALCULATE in either place for your current formula, so you should be able to remove that and get same result. I have also replaced the MAX in your return with SELECTEDVALUE:
@Anonymous  I have updated this slightly since my original post, it may help more; 
_amount =
VAR
_MAXOP = MAXX(ALL('Well List'),'Well List'[Operator])
RETURN
if(SELECTEDVALUE('Well List'[Operator]) = _MAXOP, 1, "NO")

If you are truly wanting the LATEST customer, then you'll need to do further add date into it.

[Anonymous]

Hi,

I understand it is difficult to make myself undertand but cannot share the data, however just trying to understand the concept or logic here, so, have doing lots of test, sorry for the mess on the measure, just playing around: 

_TEST1 = 
VAR _P = MAXX('Well List', 'Well List'[WellID])
VAR
    _OP = { CALCULATE(MAXX('Well List', 'Well List'[Operator]), 'Well List'[WellID] = _P)}
    
VAR
    _MAX = MAXX('Well List', 'Well List'[WellID])
VAR
    
    _MIN = { MAX('Well List'[Operator]) }

VAR
    TEST = "XTO"
VAR
    _SECOND = { MAX('Well List'[Operator])}
    RETURN
    IF(_OP = { MAX('Well List'[Operator]) }, 1, "NO")

trying to understand this: as you can see i have encapsulated the variable _OP in brackets which i believe is like having double quote correct? so this exactly measure does nothing and i do not know why, does not filter anything, however if i replace with :

IF(_OP = TEST, 1, 0)

this filters the table as i needed it, but trying to understand why if i boxed the variable in quotes or brackets like: 

"""" & MAX(WELL LIST[OPERATOR]) & """"

just does not work.

[mahoneypat]

Please try this expression.  From our other correspondence, I believe this will return blank for any operator but the one associated with the max WellID.  I forget if you have an [Amount] measure or need to sum that column, so replace SUM() with the measure if needed.

Amt Max Operator =
VAR __maxID =
    CALCULATE ( MAX ( 'Well List'[WellID] ), ALL ( 'Well List' ) )
VAR __maxoperator =
    CALCULATE (
        MAX ( 'Well List'[Operator] ),
        ALL ( 'Well List' ),
        'Well List'[WellID] = __maxID
    )
VAR __result =
    CALCULATE (
        SUM ( 'Well List'[Amount] ),
        KEEPFILTERS ( 'Well List'[Operator] = __maxoperator )
    )
RETURN
    __result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat