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] )

[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]

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]

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