Direct query and calculated column based on two tables

[mrqs]

Hi

I hope you can help me with my challenge:

I have 2 direct query tables, that are connected with relation.

Table 1 is called orderdata having 2 columns: orderstatus and custorder. 

Table 2 is called salesdata and has 3 columns: shipdate, proddate and custorder (custorder is the relation link here)

I'm trying to create a calculated column which will work as follows (this syntax is more VBA than DAX, but that's where I feel more comfortable):

testcolumn1 = if(orderdata[orderstatus]<>"closed" and salesdata[proddate]=month(now()),"IN",if(orderdata[orderstatus]="closed" and salesdata[shipdate]=month(now),"IN",""))

testcolumn2=if(orderdata[orderstatus]<>"closed" and salesdata[proddate]<>month(now),"OUT","")

I hope it makes sense.

If this can't be done in powerbi, I'll just make it in ms access or sql, where the original data is located.

thanks in advance for any help

Marek

[mrqs · Accepted solution]

Replying to myself 😉

Looks like I've found the way to make it work.

This formula seems to be giving me what I need (although the syntax is very messy in my point of view - compared to vba or c).

Tried to add some parenthesis to make it look cleaner, but then the formula just doesn't work....

if [Production_Order_Status]<>"TECO" then if Date.Month([COOIS_Commit]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else if [Production_Order_Status]="TECO" then if Date.Month([Shipping_Date]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else "OUT"

[Phil_Seamark]

You could combine the two tables using the "New Table" on the modeling table and this might be close to what you need

My New Table = SELECTCOLUMNS(
             NATURALINNERJOIN('orderdata',salesdata),
             "CustOrder",'orderdata'[custorder],
             "ProdDate",'salesdata'[proddate],
             "OrderStatus",'orderdata'[orderstatus],
             "ShipDate",'salesdata'[shipdate] ,
             "TestColumn1",
                if(
                    'orderdata'[orderstatus]<>"closed"
                    && 'salesdata'[proddate]=MONTH(NOW()),"IN",
                        IF('orderdata'[orderstatus]="closed" && 'salesdata'[shipdate]=MONTH(now()),"IN","")
                    ),
              "TestColumn2" , 
                    IF(
                        'orderdata'[orderstatus]<>"closed" 
                        && 'salesdata'[proddate]<>MONTH(NOW()),"OUT","")
             )

[mrqs]

Thanks Phil

The "new table" seems to be not active in the projects with Direct Query active (unless I'm doing something wrong).

However, I did use "Merge Queries" in the Query Editor, which gave me all the columns in a single table.

Now trying the calculated columns again...

I've got to this stage. The syntax is ok (no errors), but I'm getting message "this step results in a query that is not supported in DirectQuery mode."

I'm starting to feel hopeless.....

Entered this in the editor of custom column

column = if ([production_order_status] <> "TECO" and Date.Month([COOIS_Commit]) = Date.Month(DateTime.LocalNow())) or ([Production_Order_Status]="TECO" and Date.Month([Shipping_Date]) = Date.Month(DateTime.LocalNow())) then "IN" else "OUT"

which resulted in the following formula in the column

= Table.AddColumn(#"Renamed Columns1", "MS_IN_OUT", each if ([production_order_status] <> "TECO" and Date.Month([COOIS_Commit]) = Date.Month(DateTime.LocalNow())) or ([Production_Order_Status]="TECO" and Date.Month([Shipping_Date]) = Date.Month(DateTime.LocalNow())) then "IN" else "OUT")

[Phil_Seamark]

Yeah, you can't create measures etc as yet on Direct Query sources.  I believe that is coming but will be phased in.

The Query Editor is a much better solution in terms of making life easier on your data model.

[mrqs]

Thanks for the responses.

In case of the conditional column:

Can I enter somehow multiple contitions like:

if condition1 AND condition2 then something .... ?

The conditional column fields seems to say it's only: if condition then.... (or do I miss something?)

On daily basis I'm using VBA and thus I might still have difficulties with switching over to powerbi scipts (or limitations) 🙂

I need these 2 conditions to happen at the same time to fulfill my requirement 

example: order status is <> "TECO" and the confirmation month = current month then output is "IN"

if one of the conditions are different, then output will be "OUT"

So far, looks like I can't do that with direct query 😞

The alternative is to calculate it on the data warehouse level, which is not very handy...

I should stay with Qlik.... 😉

[mrqs · Accepted solution]

Replying to myself 😉

Looks like I've found the way to make it work.

This formula seems to be giving me what I need (although the syntax is very messy in my point of view - compared to vba or c).

Tried to add some parenthesis to make it look cleaner, but then the formula just doesn't work....

if [Production_Order_Status]<>"TECO" then if Date.Month([COOIS_Commit]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else if [Production_Order_Status]="TECO" then if Date.Month([Shipping_Date]) = Date.Month(DateTime.LocalNow()) then "IN" else "OUT" else "OUT"

[MarcelBeug · Accepted solution]

An alternative would be:

if [Production_Order_Status]<>"TECO" and Date.IsInCurrentMonth([COOIS_Commit]) or

   [Production_Order_Status]="TECO" and Date.IsInCurrentMonth([Shipping_Date])

then "IN"

else "OUT"

However, it seems that either solution breaks Query Folding / Direct Query.

I'll take a further look at this aspect.

Edit: no success as I'm trying with an Access database that gives me all dates in date/time format.

Maybe I was wrong and Direct Query did still work for you?

If not, then the following might be helpful for you:

It looks like Direct Query allows for calculation of the dates for beginning and the end of month:

Date.StartOfMonth(DateTime.Date(DateTime.LocalNow()))

Date.EndOfMonth(DateTime.Date(DateTime.LocalNow()))

Then you can try and add these as custom columns and use these for your formula.

If this all doesn't make sense then unfortunately my attempt to be helpful failed miserably in this case ...

[mrqs]

I'm using SQL server over the on premise gateway as a data source.

I've used both: mine and your first formula to calculate the columns and I did not get any error messages.

Now, it's a question of the practical test of the result, but it seems to be what I needed.

Thanks a lot for help! 🙂

[NamTQ1]

Hi mrqs,

So after reading through this article, summary if we use Direct Query then we will not be able to create custom column?

[v-huizhn-msft]

Hi @mrqs,

Do you have resolve your problem? If it does, please mark corresponding reply which will help find the solution easily. If it doesn't, please review the following solution.

Right Click->Edit Query->Conditional Column(highlighted in yellow backgroud), you will get the Conditional column dialogue box, please see the following second screenshot. 





Please enter your if...else... condition in the dialogue box, you will get expected testcolumn1, testcolumn2.

If you have other issues, please let me know.

Best Regards,
Angelia