Create measure for notifications

[RemiAnthonise]

Hi guys, 

I've followed this topic with help from @Greg_Deckler but it doesn't work for me. I use this DAX:

Notification =
VAR myCount = COUNTROWS(FILTER(SickEmployees;([Days sick]>=2)))
RETURN IF(ISBLANK(myCount);"None";myCount)

What I think it should do: it counts all rows where 'days sick' >= 2. I get 'none' beceause it's blank. If I change the measure to >=1 it gives me 74. The sum of days sick.

What I expect / want: I want to get a notification when someone is sick for >= 17 days. So the measure should be changed to

VAR myCount = COUNTROWS(FILTER(SickEmployees;([Days sick]>=2)))
RETURN IF(ISBLANK(myCount);"None";myCount). But like I said, this gives me 'none'.

You can find the sample file here in my Dropbox.

[v-yuta-msft]

@RemiAnthonise,

This issue is because you used a measure nested in countrows function, modify the measure like below:

Days sick = CALCULATE(COUNT( SickEmployees[PersonnelNr] ), ALLEXCEPT(SickEmployees, SickEmployees[PersonnelNr]))

Community Support Team _ Jimmy Tao

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

[RemiAnthonise]

Hi @v-yuta-msft @Greg_Deckler

I've modified some things and I think I'm almost there.

Here is my new sample file.

2 things:

1. I want to display the count of employees who are sick for >= 17 days until <= 21 days. I also want to display the count of employees who are sick for >= 22 days. You can see the card visuals on page 2. It is already working but only for 1 consecutive period.

2. Besides that, the visualisation of these counts, on the cards visuals, should be for this year. You['ll see a date filter on the visual level, but this doesn't work on the created column. I've set it on relative date filtering for 'this year' but it also shows the row for employee BLANK, personnelnumber 0400 which is in 2018.

I hope to hear from you. Cheers.

[Greg_Deckler]

Hmmm, not sure, I do the same thing and I get 10.

Also, I'm not sure how it is going to go with returning both a text and a numeric data type, I'm actually a bit surprised it lets you do that. See attached, Table16.

[RemiAnthonise]

Hi @Greg_Deckler Greg,

Thanks for your reply. I see you get the expected result. But the difference is: you've created a sample table with data. I had to generate my SickEmployees table as following:

SickEmployees =
SELECTCOLUMNS (
GENERATE (
SUMMARIZE (
Absences;
Absences[PersonnelNumber];
Absences[StartDate];
Absences[EndDate]
);
VAR Strt =
INT ( Absences[StartDate] )
VAR EndDte =
IF (
INT ( Absences[EndDate] ) = DATE ( 1900; 1; 1 );
TODAY ();
INT ( Absences[EndDate] )
)
RETURN
CALENDAR ( Strt; EndDte )
);
"HcmWorker_FK_PersonnelNumber"; [PersonnelNumber];
"StartDate"; [StartDate];
"EndDate"; IF ( INT ( [EndDate] ) = DATE ( 1900; 1; 1 ); BLANK (); [EndDate] );
"Date"; [Date]
)

In your case, it's just counting rows where filter >= X. In my case, see the sample I've attached, I had to create a calculated table. If I use your formula

Measure 5 =
VAR myCount = COUNTROWS(FILTER('SickEmployees';[Days Sick]>=20))
RETURN
IF(ISBLANK(myCount);"None";myCount)

I get 30 as a result. Which is logic because I have a unique row for every day my employee is sick. Because it's a calculated table. I somehow have to create a distinct countrow or whatever ... Do you have any input?

Thanks in advance, cheers.