Calculate table with top 1 value as variable

Question

Hi guys,&nbsp;Is it possible to use CALCULATETABLE using Top1 value as variable?&nbsp;topwells = CALCULATETABLE('WellList', WellList[Operator] = Top 1 value from column&nbsp;&nbsp;&nbsp;&nbsp;Thanks,

mahoneypat · Answer

Yes.&nbsp; You can use the TOPN() function in a CALCULATETABLE() or in a CALCULATE (or by itself to make a table).&nbsp; Here are a couple links to get your started.&nbsp;https://docs.microsoft.com/en-us/dax/topn-function-daxhttps://www.youtube.com/watch?v=hEUbzIcCp5w&nbsp;If this works for you, please mark it as the solution.&nbsp; Kudos are appreciated too.&nbsp; Please let me know if not.Regards,Pat

anonymous · Answer

Thanks mahoneypat&nbsp;,&nbsp;So, using the top n as a VAR and then add it to the equation?&nbsp;Would you be able to guide me a bit more?&nbsp; i used:&nbsp;&nbsp;topwells = CALCULATETABLE('WellList', WellList[Operator] = TOPN(1, WellList, WellList[Operator]))&nbsp;&nbsp;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.&nbsp;&nbsp;

mahoneypat · Answer

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

anonymous · Answer

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 · Answer

How do you know they are the current one working?&nbsp; Is there a DateTime columns too?&nbsp; A job ID?&nbsp;What is the final visual you'd like to create?&nbsp; 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.&nbsp; 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.&nbsp;Please provide sample data input and desired output.&nbsp;Regards,Pat&nbsp;

Forum Discussion

Calculate table with top 1 value as variable

7 Replies

Related content

Dynamic Variable in Calculated Columns

Understanding variable calculation

Direct Lake Model - Calculated Column/Tables

DAX Calculated Column with "variable tables" as variables.

Calculated table variables