Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table that holds UK Bank Holiday Dates and I have used a Lookup value to pull the correct number of days into another table. I use this against a weekday calc to subtract the bank hoilidays and give an actual working days per month.
The problem I am having is that when I want to use this actual working day calc in a measure, the lookup value doesn't work.
It fails to see the 3rd result (Search Value 1).
I have linked my two tables via date and checked the formatting is the same and tried a few different ways to obtain the number of bank holidays in a way that I could use as a measure, but with no success. I was hoping that with the link in place (1 to 1 - Both) I would be able to set a measure of IF(X = Y,1,0), but this won't work either as it doesn't see my table that holds the Bank Holiday Dates.
Can anyone suggest why the lookup value won't work as a measure or if there is any other way around it.
Many Thanks
Rich.
Solved! Go to Solution.
Hi @RichWyeth,
Finally, your expect result is to get the actual working days per month, right?
If so, in the weekday table, please add these calculated columns:
Holiday = LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] ) IF = IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 ) MonthName = MONTH(Weekdays[Date])
Actual working days = CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
Hi @RichWyeth,
Finally, your expect result is to get the actual working days per month, right?
If so, in the weekday table, please add these calculated columns:
Holiday = LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] ) IF = IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 ) MonthName = MONTH(Weekdays[Date])
Actual working days = CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |