Forum Discussion

SuchCT's avatar
SuchCT
Helper II
2 years ago
Solved

need help getting information from second table

Hello everyone,

I have two data tables, table1 contains the name of the employee, the country where they work, the date worked and if they worked from home or the office.

table2 contains a list of holidays per country

Holiday                Location
1/1/2024USA
1/1/2024CAN
1/1/2024UK
1/3/2024CAN
1/4/2024UK
5/3/2024CAN
7/3/2024UK
8/15/2024CAN
11/27/2024UK
12/8/2024USA



I currently have a column calculating the amount of office days pfor that employee per week. But now need for a way to have another column that says how many holidays happened during that week in that country

Name       Date                 Location         Worked  from      Office days this week
John            1/2/2024                  USA                           Office                               1
Mary1/2/2024CANOffice2
Peter1/2/2024UKOffice3
John1/3/2024USAHome1
Peter1/3/2024UKOffice3
John1/4/2024USAOffice1
Mary1/4/2024CANHome2
John1/5/2024USAHome1
Mary1/5/2024CANHome2
Peter1/5/2024UKHome3
John1/6/2024USAHome1
Mary1/6/2024CANOffice2
Peter1/6/2024UKOffice3

 

Need Help

2 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    Calendar table: I created the date table in DAX. If you already have date table, ignore this step.

    Date Table = 
 ADDCOLUMNS (
    CALENDAR("01/01/2024", "12/31/2024"),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Week number", YEAR ( [Date] ) & "-W" & WEEKNUM( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

    Data Model: Create relationships, if you already have it, ignore this step

    Adding Week to your holiday: In the table 2, holidays table, adding this column (not measure) in DAX: Reason for adding the column is typically we may have multiple requests. Recommend to do this column in  Power Query or at the source.

    Week Number = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 2'[Holiday])

     

    Similarly added two columns in your tx table, table 3, as below, in dax:

     

    Optional column: Current week number

    Current Week Number = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 3'[Date])

     

    Column for your need:

    Holiday Count this Week = 
 -- Adding as column
 Var currentWeek = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 3'[Date])
 Var HolidayWeeklyCnt = CALCULATE( COUNTROWS('Table 2'), FILTER( ALLSELECTED('Table 2'), 'Table 2'[Week Number] = currentWeek && 'Table 3'[Location] = 'Table 2'[Location]))
 Return HolidayWeeklyCnt

     

     

    May be there are shorter ways as I did not see the whole data model, wrote this.
    If you need as a measure, then tweak the DAX and tune it to your needs!

     

    Finally in the table visual

     

     

    Hope it helps!