Forum Discussion

Helper II

1 year ago

Solved

find the max value from a measure that returns multiple values

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 nee...

Super User

1 year ago

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
Helper II
1 year ago
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!
Neiners
Helper II
1 year ago
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
  Super User
  1 year ago
  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
    Helper II
    1 year ago
    now I am getting cannot convert value " of type text to type number.
    
    This still works if filtering on one row