Forum Discussion

GR_Oxnard_STU's avatar
GR_Oxnard_STU
Regular Visitor
5 years ago
Solved

Create virtual table using Max and Min dates from date prompt

Please help.  I am trying to create a virtual table using the Max and Min dates from the Date prompt.  The Date prompt uses the date from a Calendar table that is not linked to the source table (say orders).

 

I need the return to be the columns ordernumber, open date, close date, when  open date <=max(prompt selected date) and close date >=min (prompt selected date).

 

I tried the following, but it is giving me an error:

Tbl_OPEN_ORDERS =
VAR MAXDTE=MAX(CALENDAR_DTE[Date])
VAR MINDTE=MIN(CALENDAR_DTE[Date])
RETURN
CALCULATETABLE(SUMMARIZE(OrderNo, Customer_ID, Orders,Open Date ,Close Date),Open Date<=MAXDTE&&
Close Date>=MINDTE)
 
This "virtual" will be later reference in another query to exclude "orders" that are open.  
Any insight will be appreciated.  Thank you in advance.
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi  GR_Oxnard_STU ，

    Pls see the below:

    base table:

    CALENDAR_DTE = CALENDAR("2021/1/1","2021/6/1")

     

    Relationship:

     

    Then use the following measure to create a new table:

    Tbl_OPEN_ORDERS =
VAR MAXDTE =
    MAX ( CALENDAR_DTE[Date] )
VAR MINDTE =
    MIN ( CALENDAR_DTE[Date] )
RETURN
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[ordernumber],
            'Table'[open date],
            'Table'[close date]
        ),
        'Table'[open date] <= MAXDTE
            && 'Table'[close date] >= MINDTE
    )

    Final get the below:

     

    You could adjust the differnet between your dax with my dax.

    And about error The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression

    the following articles tells:If you need to invoke CALCULATE using more than one column in the condition, you need to use FILTER function, which provides a list of values instead of a condition. Since these two tables are related by the "Matter Number" field, you can use RELATED() function to compare two column values.

     

     

    Wish it is helpful for you!

     

     

    Best Regards

    Lucien

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey GR_Oxnard_STU ,

     

    and where are you struggling?

    For me it looks fine if it's a virtual table.

     

    Best regards

    Denis

    • GR_Oxnard_STU's avatar
      GR_Oxnard_STU
      Regular Visitor
      5 years ago

      The dax I wrote is giving me the following error:  The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression

       

  • GR_Oxnard_STU's avatar
    GR_Oxnard_STU
    Regular Visitor
    5 years ago

    I tried the selectcolumns approach, but it filters one of the conditions, but not the other.  iIt is filtering correctly the order start date <=max(calendar_date), but not the order end date>=min (calendar_date):

     

    OPEN_ORDERS = SELECTCOLUMNS( FILTER(TBL_ORDERS,TBL_ORDERS[start_dt].[Date]<=MAX(CALENDAR_DTE[Date])&&ORDERS[end_dt].[Date]>=MIN(CALENDAR_DTE[Date])),"ORDERNO", ORDERS[ORDERNO],
    "OPEN_START_DATE",ORDERS[start_dt],"OPEN_END_DATE",ORDERS[end_dt])
  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi  GR_Oxnard_STU ，

    Pls see the below:

    base table:

    CALENDAR_DTE = CALENDAR("2021/1/1","2021/6/1")

     

    Relationship:

     

    Then use the following measure to create a new table:

    Tbl_OPEN_ORDERS =
VAR MAXDTE =
    MAX ( CALENDAR_DTE[Date] )
VAR MINDTE =
    MIN ( CALENDAR_DTE[Date] )
RETURN
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[ordernumber],
            'Table'[open date],
            'Table'[close date]
        ),
        'Table'[open date] <= MAXDTE
            && 'Table'[close date] >= MINDTE
    )

    Final get the below:

     

    You could adjust the differnet between your dax with my dax.

    And about error The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression

    the following articles tells:If you need to invoke CALCULATE using more than one column in the condition, you need to use FILTER function, which provides a list of values instead of a condition. Since these two tables are related by the "Matter Number" field, you can use RELATED() function to compare two column values.

     

     

    Wish it is helpful for you!

     

     

    Best Regards

    Lucien

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi GR_Oxnard_STU ,

    Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

     

    Best Regards
    Lucien