Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate not working

Hi,

 

We are trying to replicate our SQL Server reports in powerbi.

 

One of the calculations works out awaiting orders.

Select NA1.WAREHOUSE_CODE,
COUNT(DISTINCT NA1.SALES_DOCUMENT_NUM) AS CanFulfill
INTO #CanFulfill
FROM #NewAwaiting NA1
WHERE (SELECT SUM(NA2.BACKORDERED) FROM #NewAwaiting NA2 WHERE NA1.SALES_DOCUMENT_NUM = NA2.SALES_DOCUMENT_NUM) = 0
AND NA1.SALES_RQD_QUANTITY > 0
GROUP BY NA1.WAREHOUSE_CODE
ORDER BY NA1.WAREHOUSE_CODE

 However if we try

Awaiting = CALCULATE(DISTINCTCOUNT(Query1[SALES_DOCUMENT_NUM]),sum(Query1[BACKORDERED])=0, Query1[SALES_RQD_QUANTITY]>0)

 

It comes back saying cant use sum in a true false.

 

What is the best way to do this?

 

Thanks

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    It's quite challenging to get an answer from this information.  It relies on knowing advanced SQL and then creating a mental model in one's head then trying to convert that to a powerbi model.

     

    The above 2 queries aren't doing the same thing anyway.  The SQL returns a temp table with 2 columns after doing a self-join on another temp table.  We don't know if Awaiting is a column or measure.

    It worries me that the powerbi table is called Query1, rather than Sales or something like that.

    I suggest that you post some data and desired output with an explanation of your model and whether you want a calculated column or measure.  Someone will help you.