Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX with conditional filter

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 b...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
mahoneypat
Microsoft Employee
6 years ago

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