topic need help building hasonefilter expression to get totals in visual in DAX Commands and Tips

need help building hasonefilter expression to get totals in visual

ChromeMystic — Thu, 21 Jul 2022 09:57:36 GMT

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.

Re: need help building hasonefilter expression to get totals in visual

lbendlin — Sat, 23 Jul 2022 16:17:25 GMT

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.

Re: need help building hasonefilter expression to get totals in visual

v-yalanwu-msft — Mon, 25 Jul 2022 02:32:09 GMT

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.

Re: need help building hasonefilter expression to get totals in visual

ChromeMystic — Wed, 27 Jul 2022 14:53:12 GMT

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.