topic Re: Dax Studio to Power BI in DAX Commands and Tips

Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 11:51:55 GMT

Hi All,

I am training on Dax and I am trying to get the top 5 clients but I keep getting an error in the Power BI Measure window but when I execute the script in Dax Studio it runs fine.

Can you help me on how to get this to work?

top 5 raised =

TOPN(

ADDCOLUMNS (

VALUES ( 'table'[ClientName] ),

"HOURS OF TICKETS", [Sum of TICKETS]

[Sum of TICKETS], DESC

)

it returns - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

thanks chaps

Re: Dax Studio to Power BI

tamerj1 — Fri, 08 Apr 2022 12:34:10 GMT

Anonymous

Indeed. This is a table of multiple columns. You can add the same code as a New Table it shoukd work. If you want to add it as a measure you can use

top 5 raised = SELECTCOLUMNS ( TOPN ( 5, ADDCOLUMNS ( VALUES ( 'table'[ClientName] ), "HOURS OF TICKETS", [Sum of TICKETS] ), [Sum of TICKETS], DESC ), [HOURS OF TICKETS] )

Re: Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 12:56:20 GMT

Hi Thanks for response. I tried to enter the code you sent back and I get the following error -

Function SELECTCOLUMNS expects a column name as argument number 2.

thanks

Re: Dax Studio to Power BI

tamerj1 — Fri, 08 Apr 2022 13:11:57 GMT

Anonymous

top 5 raised = SELECTCOLUMNS ( TOPN ( 5, ADDCOLUMNS ( VALUES ( 'table'[ClientName] ), "@HOURS OF TICKETS", [Sum of TICKETS] ), [Sum of TICKETS], DESC ), "HOURS OF TICKETS", [@HOURS OF TICKETS] )

Re: Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 13:22:32 GMT

tamerj1 thanks for the response.

Although this script is not working because the column I have contains Client Names and your script that you have edited on my behalf is looking for 1 value and Client Names contans multiple values

top 5 raised123 =

SELECTCOLUMNS (

TOPN (

ADDCOLUMNS (

VALUES ( 'table[ClientNames] ),

"@HOURS OF TICKETS", [Sum of TICKETS]

[Sum of TICKETS], DESC

"HOURS OF TICKETS", [@HOURS OF TICKETS]

)

Any chance I can get around this?

thanks

Re: Dax Studio to Power BI

tamerj1 — Fri, 08 Apr 2022 13:34:58 GMT

Anonymous

You cannot disply multiple values in a measure it has to be scallar value. If you wish you can provide an aggregation preference such max or min. Or tou need to apply further filters or just select the FIRSTNOBLANKROW. It really depends what your requirement is.

Re: Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 13:45:14 GMT

Hi tamerj1

My requirement is to show the top 5 clients -

Client 1	500
Client 2	400
Client 3	219
Client 4	111
Client 5	42

I got the result fine when I used the DAX query I added to this thread from earlier when I was using this in DAX studio.. why is it different in the power bi platform? is there a way output the same results?

Re: Dax Studio to Power BI

tamerj1 — Fri, 08 Apr 2022 14:14:34 GMT

Anonymous

In my first reply I suggested to creat it as a table.

Re: Dax Studio to Power BI

tamerj1 — Fri, 08 Apr 2022 14:21:20 GMT

Anonymous

Or otherwise jut create a table visual and drag the client into it along with a simple SUM measure. Then apply the top 5 filter from the filter pane

Re: Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 14:38:11 GMT

Hi tamerj1 Yes the reason I was trying this was because I can't find the filtering tab anymore since the new update .. is there a way to get it back?

I am currently running - Version: 2.103.881.0 64-bit (March 2022)

Re: Dax Studio to Power BI

Anonymous — Fri, 08 Apr 2022 14:42:16 GMT

hi tamerj1 I have found the filter button. thanks again