Forum Discussion
Helper IIfind the max value from a measure that returns multiple values
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] )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] ) )
Helper IIRight 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.
Super UserYes, 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] )
)