topic Re: Count employees that has sum vacancy rate below 100 on a chosen date in DAX Commands and Tips

Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Fri, 14 Jun 2024 06:10:42 GMT

Hi.

I have a table like this:

employeenumber	organizationUnit	datefrom	dateto	percent
6100000	Sales	20230101	20230131	12,3
6100000	Sales	20230201	20230531	12,3
6100000	Sales	20230801	20230806	12,3
6100000	Sales	20230807	20230831	12,3
6100000	Sales	20230901	20231231	20
6100000	Administration	20230901	20231231	25
6100000	Administration	20240201	20240214	100
6100000	Administration	20240215	20240628	100
6200000	Sales	20220101	20230913	100
6200000	Sales	20230914	20231231	100
6200000	Sales	20240101	20241231	50
6300000	Sales	20230801	20231130	40
6300000	Administration	20230801	20231231	30

The business way to tell it.
I have a table that shows how many percent employees works in exact date periods.

The end result is going to be a power bi report. I will choose one date. This date will fit into/between one or more datefrom-dateto period for each employee. Then I want to summarize this percentage for each employee. The goal is to count how many employees has below 100 percent.

I have an SSAS model where I want to write a dax code to make a value that does this.

Example:
If I choose the date 20240915 (15 of september 2024) I will get following count:

Employee 6100000 has two rows that fit into this from and to period: sum=20+25=45 (this sum is below 100 an will count as 1)
Employee 6200000 has one row that fit into this from and to period: sum=100 (this is 100 and will count as 0)
Employee 6300000 has two rows that fit into this from and to period: sum=40+30=70 (this sum is below 100 an will count as 1)

Sombody that can help me with how to build this dax code?

(Or solve it another way)

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Fri, 14 Jun 2024 09:18:54 GMT

Antallunder100:=
var filtertabell =
filter(FAK_STILLINGER,
FAK_STILLINGER[Dato fra]<=MAX(Dato[Dag])
&&
FAK_STILLINGER[Dato til]>=MAX(Dato[Dag])
)
var grupperttabell =
summarize
(filtertabell,
FAK_STILLINGER[DIM_ANSATT_ID],
"PST",sum(FAK_STILLINGER[M_STILLINGSPROSENT])
)
return
countrows(
filter(grupperttabell,[PST]<100)

)

I tried this now. Comments? Should it work?

Re: Count employees that has sum vacancy rate below 100 on a chosen date

ThxAlot — Fri, 14 Jun 2024 13:42:07 GMT

Re: Count employees that has sum vacancy rate below 100 on a chosen date

Anonymous — Mon, 17 Jun 2024 02:25:02 GMT

Hi PBIuser73 ,

Your solution is great, ThxAlot , It works like a charm! However, I noticed that more details are needed to fully understand the problem.

1. For the two date columns, it seems that they cannot be directly converted to date format in Power BI Desktop, so you can create a calculated column and use a DAX expression to convert it to date format for comparison with the selected date.

mydatefrom = VAR _year = LEFT('Table'[datefrom],4) VAR _month = MID('Table'[datefrom],5,2) VAR _day = RIGHT('Table'[datefrom],2) RETURN DATE(_year,_month,_day)

mydateto = VAR _year = LEFT('Table'[dateto],4) VAR _month = MID('Table'[dateto],5,2) VAR _day = RIGHT('Table'[dateto],2) RETURN DATE(_year,_month,_day)

2. Create measures, calculate the sum of percentages, and counts.

Sum Percent = VAR SelectedDate = SELECTEDVALUE('DAX DateTable'[Date]) RETURN CALCULATE( SUM('Table'[percent]), FILTER( 'Table', 'Table'[mydatefrom] <= SelectedDate && 'Table'[mydateto] >= SelectedDate ) )

Employees Below 100% = VAR SelectedDate = SELECTEDVALUE('DAX DateTable'[Date]) RETURN SUMX( VALUES('Table'[employeenumber]), VAR SumPercent = CALCULATE( SUM('Table'[percent]), FILTER( 'Table', 'Table'[mydatefrom] <= SelectedDate && 'Table'[mydateto] >= SelectedDate ) ) RETURN IF(SumPercent < 100, 1, 0) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Mon, 17 Jun 2024 06:17:12 GMT

Thank you for your answers.

The last part for me now, is to add more filters.
In the table i have this field "organizationUnit". I want to only count on the rows that has "Sales" in it. Where to put it?
In calculation for "Sum percent" or "Employees below 100%"

Re: Count employees that has sum vacancy rate below 100 on a chosen date

Anonymous — Tue, 18 Jun 2024 07:21:41 GMT

Hi PBIuser73 ,

You can filter by putting organizationUnit in a slicer or filter.

You can also modify the expression to:

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Wed, 19 Jun 2024 05:36:18 GMT

Thank you. I will try this. I can not use filter in report. It is actually several fields that we are not going to include in counting. And they are from other tables than the "fact" table i am counting from.

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Wed, 19 Jun 2024 05:39:06 GMT

How did you make the value Available Cnt?

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Wed, 19 Jun 2024 10:28:27 GMT

Hi Clara Gond.
My mistake. The table are not exactly like i showed you. It is built like a fact and dim structure. Like this:
Fact table:

Dim_employeenumber	Dim_position_id	Dim date from_id	dim date to_id	Percent
150	1	20230101	20230131	12,3
150	1	20230201	20230531	12,3
150	1	20230801	20230806	12,3
150	1	20230807	20230831	12,3
150	1	20230901	20231231	20
150	2	20230901	20231231	25
150	2	20240201	20240214	100
150	2	20240215	20240628	100
200	1	20220101	20230913	100
200	1	20230914	20231231	100
200	1	20240101	20241231	50
300	1	20230801	20231130	40
300	2	20230801	20231231	30

Dim table:

Dim_position_id	Name
1	Sales
2	Administration

When i try to change the code into your suggestion:

Sum Percent = 
VAR SelectedDate = SELECTEDVALUE('DAX DateTable'[Date])
RETURN
    CALCULATE(
        SUM('Table'[percent]),
        FILTER(
            'Table',
            'Table'[mydatefrom] <= SelectedDate && 'Table'[mydateto] >= SelectedDate && 'Table'[organizationUnit] = "Sales"
        )
    )

I am not able to find the "dim table". I can only filter on fields from the "fact table".
Is it possible to filter from another table than the table we are summarazing on?

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Wed, 19 Jun 2024 11:46:27 GMT

I can try one other example.
I have this code that counts fine.

NumberBelow100:=
var filtertable =
filter(TABLE1,
TABLE1[Date from]<=MAX(Date[Day])
&&
TABLE1[Date to]>=MAX(Date[Day])
)

var GroupedTable =
summarize
(filtertable,
TABLE1[DIM_ANSATT_ID],
"PST",sum(TABLE1[PERCENT])

)
return
countrows(
filter(GroupedTable,[PST]<100)

)

And now i want to add a filter that dont include counting on rows in TABLE1 based on values in a TABLE2.

I tried something like this (change in code in BOLD letters):

NumberBelow100:=
var filtertable =
filter(TABLE1,
TABLE1[Date from]<=MAX(Date[Day])
&&
TABLE1[Date to]>=MAX(Date[Day])
)

var GroupedTable =
summarize
(filtertable,
TABLE1[DIM_ANSATT_ID],
"PST",sum(TABLE1[PERCENT])
,filter(TABLE2,TABLE2[Code]<>"1429")
)
return
countrows(
filter(GroupedTable,[PST]<100)

)

Re: Count employees that has sum vacancy rate below 100 on a chosen date

Anonymous — Thu, 20 Jun 2024 06:05:42 GMT

Hi PBIuser73 ,

1. First ensure that there is a proper relationship between the two tables.

2. Modify the expression.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Fri, 21 Jun 2024 09:28:31 GMT

Hi. My two tables are connected as you show.
what i want to do (using your table as example) is to filter antother field in table 2.
Example Name='Peter'
I tried your method, but table2 wasnt coming up as a choise. Only measure values from table1

Re: Count employees that has sum vacancy rate below 100 on a chosen date

PBIuser73 — Thu, 04 Jul 2024 10:56:36 GMT

Hi. I tried your solution. I im from norway so my fieldnames are norwegian. Hope you understand anyway. I will translate if you dont understand.

Tried like this:

test3:=

var filtertabell =
filter(FAK_STILLINGER,
FAK_STILLINGER[Dato fra]<=MAX(Dato[Dag])
&&
FAK_STILLINGER[Dato til]>=MAX(Dato[Dag])
)

var grupperttabell =
summarize
(filtertabell,
FAK_STILLINGER[DIM_ANSATT_ID],
"PST",sum(FAK_STILLINGER[M_STILLINGSPROSENT])

)
return
calculate(
countrows(
filter(grupperttabell,[PST]<100)
),
filter(Stilling,Stilling[Stillingskode]<>"1429")

)

But i can not see the filter part are working. I try to change values, but the result is the same. It counts correct number of people, but when the filter are added it should reduce the number. I think maybe the filter have to come in the part where i make the variables?