Reply
Member
Posts: 95
Registered: ‎05-03-2018

Create measure for notifications

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.

 

DaysSick.jpg

 

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.

Highlighted
Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Create measure for notifications

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Member
Posts: 95
Registered: ‎05-03-2018

Re: Create measure for notifications

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.

Community Support Team
Posts: 2,535
Registered: ‎02-06-2018

Re: Create measure for notifications

@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.

Member
Posts: 95
Registered: ‎05-03-2018

Re: Create measure for notifications

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.

Totaldayssick.jpg

 

I hope to hear from you. Cheers.