Calculate table with top 1 value as variable

[Anonymous]

Hi guys,

Is it possible to use CALCULATETABLE using Top1 value as variable?

topwells = CALCULATETABLE('WellList', WellList[Operator] = Top 1 value from column

Thanks,

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Welllist:

You may create a calculated table as below.

Table = 
FILTER(
    'WellList',
    WellList[Operator] in TOPN(1,DISTINCT(WellList[Operator]),WellList[Operator])
)

Result:

Best Regards

Allan

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

[Anonymous]

I would need to create a table for this right?

I am trying to show only the values where the operator name = Max(Well list[Operator]), tis case i would like to chart just the 3 with XTO values

[mahoneypat]

Yes.  You can use the TOPN() function in a CALCULATETABLE() or in a CALCULATE (or by itself to make a table).  Here are a couple links to get your started.

https://docs.microsoft.com/en-us/dax/topn-function-dax

https://www.youtube.com/watch?v=hEUbzIcCp5w

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

Regards,

Pat

[Anonymous]

Thanks @mahoneypat ,

So, using the top n as a VAR and then add it to the equation?

Would you be able to guide me a bit more?  i used:

topwells = CALCULATETABLE('WellList', WellList[Operator] = TOPN(1, WellList, WellList[Operator]))

 but i got a :

A function 'TOPN' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[mahoneypat]

Can you provide some sample data?  For example, in what regard is the operator considered the "top"?  Alphabetically? Some measure results?

Regards,

Pat

[Anonymous]

Yes, Operator is a list of "Clients" and the top refers to the current Operator doing some work, so the top1 operator in the list is the current one working, however that name will filter the table to show all the previous work from that Operator, in python would be like:

df[df['Operator']=="Client1"]

[mahoneypat]

How do you know they are the current one working?  Is there a DateTime columns too?  A job ID?

What is the final visual you'd like to create?  Making a calculated table will not respond to slicers, so it is usually better to make a measure that can be used in dynamice visuals.  For example, you can have the user select an operator from a slicer, and then all the visuals will update to show the work by that operator, etc.

Please provide sample data input and desired output.

Regards,

Pat