Create the relationship between 2 tables in the data model

[Syndicate_Admin]

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]

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]

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]

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

[Syndicate_Admin]

Hello again

I was already able to find it and it works.

I am very happy, thank you very much.

[Syndicate_Admin]

Hello again

I have added some more columns to the order table and what is my surprise that the Time Measure is white in all rows.

Any suggestions?

[Syndicate_Admin · Accepted solution]

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

[Syndicate_Admin]

Hello again

I was already able to find it and it works.

I am very happy, thank you very much.

[Syndicate_Admin]

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

)

[v-kkf-msft]

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]

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