Allow a visual row to access all rows of a particular column in the visual

[jonnybolton]

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)

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.

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")
*/

[johnt75]

As this is purely for visual purposes, it would probably be simpler and definitely more efficient to use a visual calculation. Add a new visual calculation which gets the MAX of the [Worked (ms)] column and then formats the current row's value of that column appropriately. You can then hide the [Worked (ms)] column within the visual, so you only have the formatted value.

[jonnybolton]

Thanks, yes I did try this but I couldn't figure out how to get the maximum of all rows in the visual calculation 

[Ahmed-Elfeel]

Hi @jonnybolton,

The problem here is thats i ALLSELECTED is removing the filter from the Users table...When you used ALLSELECTED('Timesheets') alone it removed the filter context from the Users table because the relationship filter wasnt being preserved (The Users table filters were effectively ignored when calculating the maximum value)

So you can try these Approachs:

1-Use CALCULATE with ALLSELECTED on both tables:

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

-- Get max value while preserving both table relationships
VAR maxMS = 
    CALCULATE(
        MAXX(ALLSELECTED('Timesheets'), 'Timesheets'[Worked (ms)]),
        ALLSELECTED('Users')
    )

VAR maxHours = INT(maxMS / msPerHour)
VAR hourDigits = LEN(FORMAT(maxHours, "0"))

VAR thisMS = SUM('Timesheets'[Worked (ms)])
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 
    IF(isNegative, "-", "") &
    FORMAT(hh, REPT("0", hourDigits)) & ":" &
    FORMAT(mm, "00") & ":" &
    FORMAT(ss, "00") & "." &
    FORMAT(fff, "000")

2-Use VALUES to preserve context:

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

-- Get max value while preserving the relationship
VAR maxMS = 
    MAXX(
        CALCULATETABLE(
            VALUES('Timesheets'[Worked (ms)]),
            ALLSELECTED('Timesheets')
        ),
        'Timesheets'[Worked (ms)]
    )

VAR maxHours = INT(maxMS / msPerHour)
VAR hourDigits = LEN(FORMAT(maxHours, "0"))

VAR thisMS = SUM('Timesheets'[Worked (ms)])
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 
    IF(isNegative, "-", "") &
    FORMAT(hh, REPT("0", hourDigits)) & ":" &
    FORMAT(mm, "00") & ":" &
    FORMAT(ss, "00") & "." &
    FORMAT(fff, "000")

3-Use SUMMARIZE to maintain the relationship:

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

-- Get max value while maintaining table relationships
VAR VisualContext =
    SUMMARIZE(
        ALLSELECTED('Timesheets'),
        'Timesheets'[Adhoc Payment Number],
        "WorkedMS", SUM('Timesheets'[Worked (ms)])
    )

VAR maxMS = MAXX(VisualContext, [WorkedMS])

VAR maxHours = INT(maxMS / msPerHour)
VAR hourDigits = LEN(FORMAT(maxHours, "0"))

VAR thisMS = SUM('Timesheets'[Worked (ms)])
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 
    IF(isNegative, "-", "") &
    FORMAT(hh, REPT("0", hourDigits)) & ":" &
    FORMAT(mm, "00") & ":" &
    FORMAT(ss, "00") & "." &
    FORMAT(fff, "000")

Let me know if this works ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[jonnybolton · Accepted solution]

Thankyou. A more simple fix was to only return a value if thisMS is not blank.

[v-prasare]

Thanks for the response. 
we would like to know If your issue got resolved or you need any further assistance from our end? If you still need any further assistance, can you please share some sample data here so that we can review and help you accordingly?

Thanks,

Prashanth

[jonnybolton]

Yes, I resolved this, thanks for following up.