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 all,
Can you help me filter my measure by month?
I have an employee-table with start and end-dates and a date table with a workday column where each workday is 1 and all non-workdays are zero. I'm trying to create a measure which counts the total amount of workdays within the filter context. I think I got this to work with this expression:
Sum of working days = SUMX(
'Medewerkers',
CALCULATE(
SUM(Kalender[Werkdag]),
DATESBETWEEN(
'Kalender'[Date],
'Medewerkers'[Datum_in_dienst_dienstjr],
'Medewerkers'[Einddatum_contract]))
)
I sum the workday column with a datesbetween-filter with the end and start date, whitin a sumx for the employee-table.
With this measure I can see the total amount of working days on indvidual levels and/or department totals etc. However, when trying to get the amount of working days per month, for a specific person, per month I'm getting this result:
Is there a way to overcome this? A filter I can apply or relationship I have to make? There is currently no relationship between the date table and the employee table.
I can't seem to make sense of it and all the examples I found online use calculated columns, which I think doesn't work since it should be dynamic; the amount of working days have to be able to be sliced by date.
Thanks in advance!
Solved! Go to Solution.
@Anonymous
DATESBETWEEN Removes the filter from the date table. It sepends on your columns but tou may try
Sum of working days =
SUMX (
'Medewerkers',
CALCULATE (
SUM ( Kalender[Werkdag] ),
DATESBETWEEN (
'Kalender'[Date],
'Medewerkers'[Datum_in_dienst_dienstjr],
'Medewerkers'[Einddatum_contract]
),
VALUES ( Kalender[month] )
)
)
@Anonymous
DATESBETWEEN Removes the filter from the date table. It sepends on your columns but tou may try
Sum of working days =
SUMX (
'Medewerkers',
CALCULATE (
SUM ( Kalender[Werkdag] ),
DATESBETWEEN (
'Kalender'[Date],
'Medewerkers'[Datum_in_dienst_dienstjr],
'Medewerkers'[Einddatum_contract]
),
VALUES ( Kalender[month] )
)
)
Thanks, that seems to solve it!
Can you maybe explain why/how this works as a filter? I'm pretty new to DAX and like to learn more about how it works.
Thanks anyhow!
@Anonymous
Filters are actually tables. VALUES is also a table therefore, it restores back the Month column in the filter contaxt.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |