The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. PowerBI

0
votes

I have a table Sales

Date         Order No.    Order qty   Product
Jan 1, 2016     O1          2           P1
Jan 2, 2016     O1          1           P2
Jan 1, 2016     O3          4           P1
Jan 2, 2016     O7          2           P5 And so on

I am trying to write a DAX for all orders in the table 

TotalOrders = distinctcount(Sales[OrderNumber])

for e.g. here the number of distinct order IDs would be 3, order number 1, 3 and 7. So totalorders = 3

all orders = calculate([TotalOrders]/all(Sales))

I am getting an error in all orders measure.

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

2
powerbi

2 Answers

1
votes

In your measure 

all orders = calculate([TotalOrders]/all(Sales))

You are passing a table 'Sales', not the count of the item. You need to change your DAX to

all orders = DIVIDE([TotalOrders], CALCULATE(COUNTROWS(Sales), ALL(Sales))

If you data suit it you don't have to use ALL or ALLSELECTED, Just do a COUNTROWS(Sales) You can create the measure which countrows. It is best practice to use the DIVIDE function rather than '/' as it handle errors such as divide by zero and nulls.

0
votes

Replace / with , (comma).

Query becomes: 

all orders = calculate([TotalOrders],all(Sales))

It works perfectly now. So DAX is telling powerBI-hey, take totalorders from table sales.