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...
Need Help
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!