Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Create the relationship between 2 tables in the data model

Good afternoon

I have created a Calendar Table as follows:

Calendar =

where _cal = CALENDAR(MIN('Orders'[Start_Date]), MAX('Orders'[End_Date]))
return
ADDCOLUMNS(_cal,"Year", YEAR([Date]), "Month Num", MONTH([Date]), "Month Name", FORMAT([Date], "mmmm"))

The orders table is as follows:

I have created a DatePicker whose values are linked to the Calendar table where the user can select a range of dates between Start_Date and End_Date. The problem I am having is that I need to create the relationship between both tables because when I select the values in the DatePicker the selection is not made between the selected dates.

How should I create the relationship in the model? I don't know how to relate the Date field to the order table with both Start_Date and End_date.

Thank you very much in advance

  • Syndicate_Admin's avatar
    Syndicate_Admin
    4 years ago

    Hello

    Troubleshoot the problem. In this measure of time in the AllSELECTED function you add all the columns that you are going to show in the report.

    Time Measure =
    WHERE SelectedTable =
    FILTER (
    ALLSELECTED ( Orders[Start Date], Orders[End Date],Orders[ID_BATCH],Orders[SIG_BY_ID] ),
    Pmx[End Date] <> BLANK ()
    )
    WHERE CurrentStart = MAX ( Pmx[Start Date] )
    WHERE PreviousDatesTable = FILTER ( SelectedTable, Pmx[Start Date] < CurrentStart )
    WHERE PreviousEnd = MAXX ( PreviousDatesTable, Pmx[End Date] )
    WHERE FirstSelectedDate = MINX ( PreviousDatesTable, Pmx[CPK_SIG_TIMESTAMP] )
    WHERE TimeDifference = DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
    WHERE Result = DIVIDE ( TimeDifference, 60, " " )
    RETURN
    Result
    Thank you for the help you have given me, thank you very much

10 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Syndicate_Admin ,

     

    Please try the measure and show items when it is not blank.

     

    Measure = 
COUNTROWS (
    FILTER (
        Orders,
        Orders[Start_Date].[Date] >= MIN ( 'Calendar'[Date] )
            && Orders[End_Date].[Date] <= MAX ( 'Calendar'[Date] )
    )
)


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      I have added the measure and the same thing continues to happen to me. If I have it active it comes out like this

      And if I remove the measure now I lose the first and second row

      I need that when I deactivate the measure that I do not need to visualize in the table, the records of the Table continue to look as if there were

      Thank you and I look forward to it

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    In case it is useful for you, here I leave the Calendar table as I have created it:

    Calendar =
    where _cal = CALENDAR(MIN('Pmx'[Start Date]), MAX('Pmx'[End Date]))
    return
    ADDCOLUMNS(_cal,"Year", YEAR([Date]), "Month Num", MONTH([Date]), "Month Name", FORMAT([Date], "mmmm"))
    The calendar table is created with the minimum and maximum values of the Start Date and End Date fields, therefore the dates to be selected will always be in ranges that exist in the Orders table.

    And this is the measure linked to the table that was simply to check that the records that are returned to me are with value 1, since the selected ranges are always going to be within date range.

    InDateRange =
    //Measure to know what rows are between dates selected by user
    //0 - Not in range
    //1 - In range
    WHERE _rangeStart =
    FIRSTDATE ( 'Calendar'[Date] )
    WHERE _rangeEnd =
    LASTDATE ( 'Calendar'[Date] )
    RETURN
    IF (
    SELECTEDVALUE ( Pmx[Start Date] ) >= _rangeStart
    && SELECTEDVALUE ( Pmx[End Date] ) <= _rangeEnd
    && SELECTEDVALUE ( Pmx[End Date] ) <> BLANK(),
    1,
    0
    )
  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    As for this measure you told me to add

    Measure = COUNTROWS ( FILTER ( Orders, Orders[Start_Date].[ Date] >= MIN ( 'Calendar'[Date] ) && Orders[End_Date]. [Date] <= MAX ( 'Calendar'[Date] ) ) )

    I have it added on the screen on a Card on the screen and also in the table.

    When the measure is active in the table, the number of rows I see is the same as the number it shows me on the Card.

    But when I turn off the measure in the table, I visually see fewer rows in the table and the same number in the Card. It takes away the one that is empty and that I should keep.

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Syndicate_Admin ,

       

      Please try enabling the option "Show items with no data".

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

       

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        4 years ago

        Hello

        I am not able to find that menu, where do I have to press please?

        I've been clicking on the measure etc... and I don't see it

        Thank you