find the max value from a measure that returns multiple values

[Neiners]

I have multiple measures that are returning the datediff from the last time an item checked in from todays date. The problem that I am running into is that some items have multiple entries that I need to account for when looking that item up in another table. I separate those entries with UNICHAR(10). What I am trying to do is find the max values (datediff) from all the measures combined. 

I have tried:

var _union =
UNION(
ROW("Days", [Table1 days since last checked in]),
ROW("Days", [Table2 Days since last checked in]),
ROW("Days", [Table3 days since last checked in]),
ROW("Days", [Table4 days since last checked in]),
ROW("Days", [Table5 days since last checked in]),
ROW("Days", [Table6 days since last checked in])
)
var _series =
GENERATE(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE(_union, UNICHAR(10), "|")) +1, 1),
var _index = [Value]
RETURN ROW("Days", PATHITEM(SUBSTITUTE(_union, UNICHAR(10), "|"), _index)))
RETURN
MAXX(_series, [Value])

but I get the error of multiple values was supplied

I have tried this:

MAXX(
{
[Table1 days since last checked in],
[Table2 days since last checked in],
[Table3 days since last checked in],
[Table4 days since last checked in],
[Table5 days since last checked in],
[Table6 days since last checked in]
},
[Value])

but if a Table has multiple entries separated by unichar(10) it is providing the unichar(10) results where I  only need the max value from that measure

Example data: I want the MAX days since....to report back 363 because that is the highest value. One thing to note is that each of the columns in the example below are caclulated columns in another table. I am basically consolidating data from multiple tables into a combined table and I am using measures to create the calculated columns for that combined table.

MAX days since...	Table1 days since last checked in	Table2 days since last checked in	Table3 days since last checked in	Table4 days since last checked in	Table5 days since last checked in	Table6 days since last checked in
104 99 35 363		104		104 99 35 363

[MarkLaf · Accepted solution]

Just to confirm, is the format of the data something that you cannot change? You are describing calculating columns from other tables and consolidating in one table - is there a reason you are not doing this in Power Query? If not, you should reshape the data to better fit your analytical needs and then load into your model just what you need for analysis and visuals.

That said, you are on the right track with your first attempt but are missing some steps. You are getting the "multiple values supplied" error because, specifically, _union is a table in the below context but you are trying to perform string manipulation:

SUBSTITUTE(_union, UNICHAR(10), "|")

Something like this should work instead.

Given these measures:

Table1 days since last checked in = 
""

Table2 days since last checked in = 
"104"

Table3 days since last checked in = 
""

Table4 days since last checked in = 
"104
99
35
363"

The following measure gets you the number 363:

MaxValFromText = 
VAR _orig = 
{ 
    [Table1 days since last checked in], 
    [Table2 days since last checked in], 
    [Table3 days since last checked in], 
    [Table4 days since last checked in] 
}
VAR _noEmpty = FILTER( _orig, [Value] <> "" )
VAR _toPath = CONCATENATEX( _noEmpty, SUBSTITUTE( [Value], UNICHAR(10), "|" ) , "|" )
VAR _count = PATHLENGTH( _toPath )
VAR _iter = GENERATESERIES( 1, _count )
VAR _list = GENERATE( _iter, ROW( "Item", VALUE( PATHITEM( _toPath, [Value], TEXT ) ) ) )
RETURN
MAXX( _list, [Item] )

[MarkLaf · Accepted solution]

Just to confirm, is the format of the data something that you cannot change? You are describing calculating columns from other tables and consolidating in one table - is there a reason you are not doing this in Power Query? If not, you should reshape the data to better fit your analytical needs and then load into your model just what you need for analysis and visuals.

That said, you are on the right track with your first attempt but are missing some steps. You are getting the "multiple values supplied" error because, specifically, _union is a table in the below context but you are trying to perform string manipulation:

SUBSTITUTE(_union, UNICHAR(10), "|")

Something like this should work instead.

Given these measures:

Table1 days since last checked in = 
""

Table2 days since last checked in = 
"104"

Table3 days since last checked in = 
""

Table4 days since last checked in = 
"104
99
35
363"

The following measure gets you the number 363:

MaxValFromText = 
VAR _orig = 
{ 
    [Table1 days since last checked in], 
    [Table2 days since last checked in], 
    [Table3 days since last checked in], 
    [Table4 days since last checked in] 
}
VAR _noEmpty = FILTER( _orig, [Value] <> "" )
VAR _toPath = CONCATENATEX( _noEmpty, SUBSTITUTE( [Value], UNICHAR(10), "|" ) , "|" )
VAR _count = PATHLENGTH( _toPath )
VAR _iter = GENERATESERIES( 1, _count )
VAR _list = GENERATE( _iter, ROW( "Item", VALUE( PATHITEM( _toPath, [Value], TEXT ) ) ) )
RETURN
MAXX( _list, [Item] )

[Neiners]

It was working like a charm when I was filtering on a single row. When I removed the filter, I got the generate series cannot be blank error.

[MarkLaf]

That is probably happening if you have instances where all your inputs are empty strings (""). I think the below would handle it. You can see that it is mostly the same, except we ensure we aren't dealing with an empty set before moving on.

MaxValFromText_BlankHandling = 
VAR _orig = 
{ 
    [Table1 days since last checked in], 
    [Table2 days since last checked in], 
    [Table3 days since last checked in], 
    [Table4 days since last checked in] 
}
VAR _noEmpty = FILTER( _orig, [Value] <> "" )
RETURN
IF( 
    NOT ISEMPTY( _noEmpty ),
    VAR _toPath = CONCATENATEX( _noEmpty, SUBSTITUTE( [Value], UNICHAR(10), "|" ) , "|" )
    VAR _count = PATHLENGTH( _toPath )
    VAR _iter = GENERATESERIES( 1, _count )
    VAR _list = GENERATE( _iter, ROW( "Item", VALUE( PATHITEM( _toPath, [Value], TEXT ) ) ) )
    RETURN
    MAXX( _list, [Item] )
)

[Neiners]

now I am getting cannot convert value " of type text to type number.

This still works if filtering on one row

[MarkLaf · Accepted solution]

That means there are items in your line-break delimeters that cannot be converted to a number with VALUE. I'd recommend troubleshooting by switching out the final MAXX RETURN with _toPath to then inspect the pipe-delimited values you are dealing with.

If you are fine with just ignoring items that can't be converted (rather than figuring out some kind of parsing pattern to extract and then convert the number), then the following I think would work:

MaxValFromText_BlankHandling_TextHandling = 
VAR _orig = 
{ 
    [Table1 days since last checked in], 
    [Table2 days since last checked in], 
    [Table3 days since last checked in], 
    [Table4 days since last checked in]
}
VAR _noEmpty = FILTER( _orig, [Value] <> "" )
RETURN
IF( 
    NOT ISEMPTY( _noEmpty ),
    VAR _toPath = CONCATENATEX( _noEmpty, SUBSTITUTE( [Value], UNICHAR(10), "|" ) , "|" )
    VAR _count = PATHLENGTH( _toPath )
    VAR _iter = GENERATESERIES( 1, _count )
    VAR _list = GENERATE( 
        _iter, 
        ROW( 
            "Item", 
            IFERROR( 
                VALUE( PATHITEM( _toPath, [Value], TEXT ) ), 
                BLANK()   //   <------ default to blank if VALUE fails
            ) 
        ) 
    )
    RETURN
    MAXX( _list, [Item] )
)

[Neiners]

Right now, I am choosing to ignore the errors and this latest update works! I created another measure just using the _toPath for me to troubleshoot and briefly looking down the list, I didn't see any non-numbers. I did however see a couple where it had the pipe first (|#|#) followed by a number instead of (#|#) so I'm wondering if those blanks in front of the pipes are the cause for the error.

[MarkLaf]

Yes, if you have items with "|#|#" as the full path, that means PATHITEM( _toPath, 1, TEXT ) = "", which will produce the text error in VALUE. This means that some of your original text has the line-break as first character.

The risk with using IFERROR is that it may later on hide different issues that you want to address rather than ignore.

If it were me, I'd put some specific empty string handling at the _list step, like:

MaxValFromText_BlankHandling_TextHandling3 = 
VAR _orig = 
{ 
    [Table1 days since last checked in], 
    [Table2 days since last checked in], 
    [Table3 days since last checked in], 
    [Table4 days since last checked in],
    // add line break issue
    "
    10
    20
    "
}
VAR _noEmpty = FILTER( _orig, TRIM( [Value] ) <> "" ) // adding TRIM, a bit more robust
RETURN
IF( 
    NOT ISEMPTY( _noEmpty ),
    VAR _toPath = CONCATENATEX( _noEmpty, SUBSTITUTE( [Value], UNICHAR(10), "|" ) , "|" )
    VAR _count = PATHLENGTH( _toPath )
    VAR _iter = GENERATESERIES( 1, _count )
    VAR _list = GENERATE( 
        _iter, 
        ROW( 
            "Item", 
            VAR _txt = PATHITEM( _toPath, [Value], TEXT ) RETURN
            IF( TRIM( _txt ) <> "", VALUE( _txt ) ) // empty string handling
        ) 
    )
    RETURN
    MAXX( _list, [Item] )
)

[Neiners]

I have tried consolidating in Power Query but I don't get the same results with DAX and calculated tables.

Thank you so much! This works like a charm! 

[grazitti_sapna]

Hi @Neiners,

To fix this issue use below DAX

Max Days Since Last Checked In :=
VAR _table =
UNION(
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table1 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table1 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
),
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table2 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table2 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
),
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table3 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table3 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
),
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table4 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table4 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
),
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table5 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table5 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
),
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Table6 days since last checked in], UNICHAR(10), "|"))),
"Value",
VALUE(PATHITEM(SUBSTITUTE([Table6 days since last checked in], UNICHAR(10), "|"), [Value]))
),
"Days", [Value]
)
)
RETURN
MAXX(_table, [Days])

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Neiners]

I changed the "Value" to "Result" and now I am getting another error. The arguments for Generate Series function cannot be blank

[Neiners]

I am getting an error "ADDCOLUMNS" cannot add column [Value] since it already exists