topic Re: Filter in DAX Commands and Tips

Filter

Anonymous — Sat, 02 Jul 2022 06:22:02 GMT

Ik heb een tabel met:

datum, naam medewerker, roosteruren, waarvan uren verlof, uren ziek, uren gewerkt.
nu wil ik een DAX functie maken om het percentage te berekenen van aantal medewerkers die in de geselecteerde periode niet ziek zijn geweest (som van de kolom waarde ziek is 0) ten opzichte van alle medewerkers.

wie kan mij hier bij helpen?

Re: Filter

tamerj1 — Sat, 02 Jul 2022 06:53:36 GMT

Hi Anonymous
Please share sample file.

Re: Filter

Anonymous — Sun, 03 Jul 2022 09:59:07 GMT

This is the data.

Date	Name	Scheduled working time	leave hours	hours of illness	working hours
15-5-2022	employee 1	8	0	0	8
16-6-2022	employee 1	8	0	0	8
17-5-2022	employee 2	8	8	0	0
18-5-2022	employee 3	8	0	8	0
19-6-2022	employee 3	8	0	0	8
20-6-2022	employee 4	8	0	0	8
21-6-2022	employee 5	8	0	0	8
22-6-2022	employee 2	8	0	8	0
23-6-2022	employee 5	8	0	0	8
24-6-2022	employee 6	8	0	0	8
25-5-2022	employee 5	8	0	8	0
26-6-2022	employee 6	8	0	0	8
27-6-2022	employee 6	8	0	0	8
28-5-2022	employee 4	8	0	0	8
29-5-2022	employee 6	8	0	0	8

The outcome should be:

nr. employee not haven been ill at all in june: 5 (83,3% of total employee's)

nr. employee not haven been ill at all in may: 4 (66,7% of total employee's)

nr. employee not haven been ill at all: 3 (50,0% of total employee's)

name june may total

employee 1	0	0	0
employee 2	8	0	8
employee 3	0	8	8
employee 4	0	0	0
employee 5	0	8	8
employee 6	0	0	0

Re: Filter

tamerj1 — Tue, 05 Jul 2022 04:24:26 GMT

Anonymous
Appologies for the late reply. Here is a sample file with the solution https://we.tl/t-9aEQxioBnY

Hope this is what you're looking for

Haven't Been Ill At All Count = VAR TotalCount = DISTINCTCOUNT ( Data[Name] ) VAR IllCount = CALCULATE ( DISTINCTCOUNT ( Data[Name] ), Data[hours of illness] > 0 ) VAR NotIllCount = TotalCount - IllCount VAR NotIllPercent = FORMAT ( DIVIDE ( NotIllCount, TotalCount ), "Percent" ) RETURN IF ( Not ISBLANK ( TotalCount ), NotIllCount & " (" & NotIllPercent & ")" )

Re: Filter

Anonymous — Tue, 05 Jul 2022 15:39:11 GMT

thank you very much for this solution. It works perfectly.

This will help me also with writing other functions.