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.
I am currently working on a report about the login/logout times of our agents comparing them to the planned shifts. One of the values i have to deliver is an overview of how often they logged in 6 minutes or more after their shift has started.
With help in my last post here I got the formula working to properly show a 1 for every day where they logged in late. (Solved: Re: Calculate how often a threshold between two ti... - Microsoft Power BI Community)
loginCount =
VAR summaryTable =
CALCULATETABLE (
ADDCOLUMNS ( VALUES ( sedShifts[shift] ), "@loginDiff", _calc[loginDiff] ),
// get difference between shift start and actual login time0
_date[lastMonth]
= TRUE () // load all dates from last month
//_date[date] >= TODAY () - 7
)
VAR lateTimes =
COUNTROWS ( FILTER ( summaryTable, [@loginDiff] >= 6 ) ) // count rows with more than 6 minutes late time
RETURN
lateTimes
this results in the following table per agent (ignore the loginWarning)
The Total only shows the last value. I googled the problem and found that I have to use "HASONEFILTER" to get it to create the right value. The Problem is, that I cannot get it to work. I cannot select any of the values in my Formula as the columnname that HASONEFILTER asks for.
I would be very thankful for any pointers on how to build said formula so I get a proper total.
Solved! Go to Solution.
Hi, @ChromeMystic ;
Try it.
Newmeasure =
IF (
ISINSCOPE ( sedShifts[dateshort] ),
[logincount],
SUMX ( SUMMARIZE ( 'sedShifts', [dateshort], "1", [logincount] ), [1] )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks guys - I was only able to check the replies today but I got correct results now. Not quite as good looking as I want them yet but they work, that is the important thing.
Hi, @ChromeMystic ;
Try it.
Newmeasure =
IF (
ISINSCOPE ( sedShifts[dateshort] ),
[logincount],
SUMX ( SUMMARIZE ( 'sedShifts', [dateshort], "1", [logincount] ), [1] )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You need to apply HASONEVALUE to [dateShort]. Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |