How to use a dynamic string variable

[Anonymous]

Hi guys,

Hope you can help me, i have this table:

the data is not always sorted so, i am looking for the operator name where the id is the highest: this the following i was able to get the operator name by max wellid,

_CURRENT = 
VAR TOPWELL = MAX('Well List'[WellID])
VAR _topclient = 
CALCULATE(MAX('Well List'[Operator]), KEEPFILTERS( FILTER( ALL('Well List'[WellName], 'Well List'[Operator], 'Well List'[WellID]), 'Well List'[WellID] = TOPWELL)))
return _topclient

how can i identify in the main table the all the operators with the name as result of the measure? i tried:

IF( _topclient IN VALUES(Operator name), 1, 0)

but this returns all values as 1. Any idea?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Greg_Deckler]

@Anonymous - Not sure I completely understand, but perhaps this:

Measure = IF(MAX([Operator]) = [_CURRENT],1,0)

[Anonymous]

if i just return the variable _topclient :

_CURRENT = 
VAR TOPWELL = MAX('Well List'[WellID])
VAR _topclient = 
CALCULATE(MAX('Well List'[Operator]), KEEPFILTERS( FILTER( ALL('Well List'[WellName], 'Well List'[Operator], 'Well List'[WellID]), 'Well List'[WellID] = TOPWELL)))
return _topclient

the result is this:

 so seems it is filtering row by row and not giving the proper result.

[Greg_Deckler]

@Anonymous - I'm still now 100% clear but you may need an ALL or ALLEXCEPT in there to break out of the current context of the visual/row.

[Anonymous]

Any slightly idea where i should apply it?

[Anonymous]

Hi @Anonymous ,

Please try to update the formula of measure as below:

Flag =
VAR TOPWELL =
    CALCULATE ( MAX ( 'Well List'[WellID] ), ALL ( 'Well List' ) )
VAR _topclient =
    CALCULATE ( MAX ( 'Well List'[Operator] ), 'Well List'[WellID] = TOPWELL )
RETURN
    IF ( MAX ( 'Well List'[Operator] ) = _topclient, 1, 0 )

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,

Thanks for your answer, i already tried that way before with the result but, i see in your example you have two "JJ", so basically how would you add the 1 value in all the JJ's in the column?

[Anonymous · Accepted solution]

Hi @Anonymous ，

Please try to create a calculated column with below formula to replace the original measure:

Column = 
VAR _topclient =
    CALCULATE (
        MAX ( 'Well List'[Operator] ),
        FILTER (
            ALL ( 'Well List' ),
            'Well List'[WellID] = MAX ( 'Well List'[WellID] )
        )
    )
RETURN
    IF (  'Well List'[Operator]  = _topclient, 1, 0 )

Best Regards

Rena

[parry2k]

@Anonymous I don't understand why we are all after creating the column when measure can do the job. Do we understand the difference between creating measure vs columns and how it has an impact on the model?

[parry2k]

@Anonymous @Anonymous solution should work, not sure why is not showing 1 for each JJ operator, I created sample data and used following measure 

Top Operator = 
VAR __topId = CALCULATE ( MAX ( Well[Id] ), ALL (  Well ) )
VAR __topOpertor = CALCULATE ( MAX ( Well[Operator] ), Well[Id] = __topId )
RETURN IF ( MAX ( Well[Operator] ) = __topOpertor, 1, 0 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[Anonymous]

Hi @Greg_Deckler ,

Thanks for your answer, i still got the same result as 1, i know Max will be interating row by row but wondering why not recognizing the _topclient variable.