Forum Discussion

mrqs's avatar
mrqs
Advocate I
9 years ago
Solved

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

Need Help
  • mrqs's avatar
    mrqs
    9 years ago

    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's avatar
    MarcelBeug
    9 years ago

    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:

     

9 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      mrqs
      Advocate I
      9 years ago

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

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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