Forum Discussion
Direct query and calculated column based on two tables
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"
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 ... :smileyfrustrated:
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.... ;)
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"
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 ... :smileyfrustrated:
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! :)
Hi mrqs,
So after reading through this article, summary if we use Direct Query then we will not be able to create custom column?
