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]), "Mo...
  • 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
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
)