cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrqs Frequent Visitor
Frequent Visitor

Direct query and calculated column based on two tables

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

regards
Marek
2 ACCEPTED SOLUTIONS

Accepted Solutions
mrqs Frequent Visitor
Frequent Visitor

Re: Direct query and calculated column based on two tables

Replying to myself Smiley Wink

 

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"

regards
Marek

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Direct query and calculated column based on two tables

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 ... Smiley Frustrated

 

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Phil_Seamark Super Contributor
Super Contributor

Re: Direct query and calculated column based on two tables

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","")
             )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

mrqs Frequent Visitor
Frequent Visitor

Re: Direct query and calculated column based on two tables

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")

 

regards
Marek
v-huizhn-msft Super Contributor
Super Contributor

Re: Direct query and calculated column based on two tables

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. 

1.PNG

2.PNG

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

Phil_Seamark Super Contributor
Super Contributor

Re: Direct query and calculated column based on two tables

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

mrqs Frequent Visitor
Frequent Visitor

Re: Direct query and calculated column based on two tables

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) Smiley Happy

 

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 Smiley Sad

 

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

 

I should stay with Qlik.... Smiley Wink

 

 

regards
Marek
mrqs Frequent Visitor
Frequent Visitor

Re: Direct query and calculated column based on two tables

Replying to myself Smiley Wink

 

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"

regards
Marek

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Direct query and calculated column based on two tables

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 ... Smiley Frustrated

 

Specializing in Power Query Formula Language (M)

View solution in original post

mrqs Frequent Visitor
Frequent Visitor

Re: Direct query and calculated column based on two tables

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! Smiley Happy

regards
Marek
NamTQ1 Visitor
Visitor

Re: Direct query and calculated column based on two tables

Hi mrqs,

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 132 members 1,783 guests
Please welcome our newest community members: