"The expression refers to multiple columns..." error for summarize

[jobowman]

I need help getting this query to return properly.  I'm trying to get a by-client count of orders.  I have reviewed the syntax many times and cannot figure out how to get it to cooperate.

Orders = SUMMARIZE('remotedb tblorders','remotedb tblorders'[FromName],"Orders",DISTINCTCOUNT('remotedb tblorders'[OrderNo]))

[v-jiascu-msft]

Hi @jobowman,

The SUMMARIZE returns a table result while a measure or a calculated column requires a scala value. If you use this formula to create a calculated table, you will get no errors. What were you going to do? The SUMMARIZE should be used as a parameter of other functions in a measure. Such as SUMX, COUTNX. 

Orders =
SUMX (
    SUMMARIZE (
        'remotedb tblorders',
        'remotedb tblorders'[FromName],
        "Orders", DISTINCTCOUNT ( 'remotedb tblorders'[OrderNo] )
    ),
    [Orders]
)

Best Regards,

[jobowman]

Thanks for helping!  I'm trying to get a count of the orders for each individual client, which is the first column of a SQL call I'm trying to recreate.  I'm assuming that if this function returns a table, I wouldn't be able to apend the following columns to it,?  If this is the case, do you have any suggestions on how I tackle this?

SELECT Fromname

COUNT(distinct tblorders.OrderNo) As Orders,
COUNT(if(tblorders.picked_on Is Null and tblorders.packed_on IS Null and tblorders.shipped_on IS Null,1,NULL)) as Left_To_Scan,
COUNT(distinct tblorders.OrderNo)-COUNT(distinct tblshipconf.OrderNo) As Left_to_Ship

FROM tblorders
left join stopped_orders on (tblorders.orderno = stopped_orders.orderno)
left join tblshipconf on (tblorders.orderno=tblshipconf.orderno)
WHERE stopped_orders.orderno is Null and if(weekday(now())>=1,
tblorders.created >= DATE(NOW()),
tblorders.created >= DATE(NOW()) - INTERVAL 3 DAY)
GROUP by clients.ClientName

[v-jiascu-msft]

Hi @jobowman,

You can try the DAX function dax/addcolumns-function-dax to add a column. But the result is still a table. 

Can you share a sample and the expected result?

Best Regards,

[Anonymous]

HI @v-jiascu-msft ,

I want to create a measure(SDP), which i will add the measure (sdp) to my report filter page. 

My requirement is to filter certain ids from the list.

Example : I have a customer table with customer_id column has a list of id's, I want to filter our certain customers from the columns. 

Customer . Customer_id not in ("123","234",235","345","456") .

My customer_id is so huge that i cant add the column directly to the filter and select those values manually. How do I create a measure for this requirement.

Thanks in advance