Dax Studio to Power BI

[Anonymous]

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(

5,

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

[Anonymous · Accepted solution]

hi @tamerj1 I have found the filter button. thanks again

[Anonymous]

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)

[Anonymous]

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?

[tamerj1]

@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

[tamerj1]

@Anonymous 

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

[Anonymous]

@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 (

5,

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 

[tamerj1]

@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. 

[tamerj1]

@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]
)

[Anonymous]

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 

[tamerj1]

@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]
)