How to allow a visual row to access all rows of a particular column in the visual?

Question

I have a visual using columns from the Timesheets and Users tables, using the relationship on [_userID] (Users-to-Timesheets, one-to-many, single). Each row of the visual is uniquely defined by Timesheets[Adhoc Payment Number], but there are also many other columns used in the visual. The visual is filtered by columns from both tables.

One of the columns in the visual is Timesheets[Worked (ms)], measured in milliseconds. For each row of the visual, I would like to convert this number into a formatted text string hh:mm:ss:fff. The complexity comes from the desire to pad this string with 0s where required, so that each string has the same length (e.g. if the longest timesheet was 10 hours, every timesheet less than 10 hours should have a 2-digit hour e.g. 09:30:00:000; similarly if max >= 100, every string should have a 3-digit hour).

In this example, the max value is 85635743 (about 23 hours)

Before

This requires the measure to be able to obtain the maximum value of Timesheets[Worked (ms)] in the visual, removing the row context but respecting the filters used to create the visual. I think this can be done with ALLSELECTED.

After applying the measure, the correct max value (85635743) is calculated. However, the Timesheets[Adhoc Payment Number] values are no longer unique, and the maximum Timesheets[Worked (ms)] has changed. This new max value is the same as when the Users filter on the visual is manually removed - suggesting the measure has removed the relationship between Timesheets and Users, and Users is no-longer being filtered.

After

How can I fix this?

Worked = 
VAR msPerHour = 1000 * 60 * 60
VAR msPerMin  = 1000 * 60
VAR msPerSec  = 1000

VAR maxMS      = MAXX( ALLSELECTED( 'Timesheets'), Timesheets[Worked (ms)] ) -- max value in column
VAR maxHours   = INT( maxMS / msPerHour )
VAR hourDigits = LEN( FORMAT( maxHours, "0" ) )

VAR thisMS     = SUM( 'Timesheets'[Worked (ms)] ) -- current row value
VAR isNegative = thisMS < 0
VAR absMS      = ABS( thisMS )

VAR hh  = INT( absMS / msPerHour )
VAR mm  = INT( MOD( absMS, msPerHour ) / msPerMin )
VAR ss  = INT( MOD( absMS, msPerMin ) / msPerSec )
VAR fff = MOD( absMS, msPerSec )

RETURN maxMS
/*
    IF( isNegative, "-", "") &
    FORMAT( hh, REPT( "0", hourDigits ) ) & ":" &
    FORMAT( mm, "00") & ":" &
    FORMAT( ss, "00") & "." &
    FORMAT( fff, "000")
*/

davidebacci · Accepted Answer · 2025-09-29 15:21:51Z

1

It's because you're returning a value for every row. Normally, DAX will suppress rows where the value returned is blank.

Try something like:

IF(NOT ISBLANK(thisMS), maxMS)

answered Sep 29 at 15:21

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

jonnybolton16 Sep 29 at 16:18

Interesting, thanks. Why would thisMS be blank, since every row has a value for that column?

davidebacci Sep 29 at 17:43

Probably best to check if my solution actually works and if it does, I can expand on the theory.

jonnybolton16 Sep 29 at 18:08

Sure, I'll check when I'm back in work tomorrow!

jonnybolton16 Sep 30 at 7:09

It worked! Thankyou

davidebacci Sep 30 at 7:44

Read the answer here for an explanation. You might want to upvote the question and answer both here and there to also help others in future. stackoverflow.com/questions/65806735/…

jonnybolton16 Oct 2 at 5:37

Thanks. So if I understand correctly, this is happening because there are some selected Users that don't have any Timesheets in the current selection; normally these Users are suppressed in the visual, but the measure still executes for them and finds that thisMS is blank. Is that correct?

Collectives™ on Stack Overflow

How to allow a visual row to access all rows of a particular column in the visual?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related