topic Re: Skipping blanks in valuelookup in DAX Commands and Tips

Skipping blanks in valuelookup

MSW — Mon, 04 Apr 2022 00:03:06 GMT

Hello,

Is it possible to use valuelookup and skip zeros/blanks in order to return only the whole number associated to an ID. I have a column that only some of the unique IDs have a value per row. The dataset has many duplicated IDs which is why I'm getting the 'multiple values were supplied where a single value was expected'. Is it possible to use NoBlankrows or another type filter with valuelookup to bypass the blanks in the column?

Thank you for the help and clarification.

Re: Skipping blanks in valuelookup

Greg_Deckler — Mon, 04 Apr 2022 01:11:50 GMT

MSW Assuming you are talking about LOOKUPVALUE function, use MAXX(FILTER(...),...) instead. More reliable and allows you to explicitly control the filtering.

Re: Skipping blanks in valuelookup

MSW — Mon, 04 Apr 2022 17:26:45 GMT

Greg_Deckler Thanks that works except that if I do not want the max value and I want the value associated to each row by ID.

IE:

Table 1:

Customer ID

123

345

123

567

Table 2:

Customer ID	Merged
123	123 789
345	345 134
123	123 876
567	567 214

I want to return the merged values to table one but the only way I have to connect the two is by customerID. Is it possible to look up the value and return it based on the row? The other option I have that does not appear to be working is in another table I have the merged column that is already associated to each customer ID. Greatly appreciate the help

Re: Skipping blanks in valuelookup

Greg_Deckler — Mon, 04 Apr 2022 21:37:44 GMT

MSW You could use CONCATENATEX instead of MAXXX to pull back multiple values. I'm having some issues trying to visualize your setup. You might try adding an index maybe? I'm not understanding what ties the second 123 in the first table to the second 123 in the second table. You'd have to have everything in the same order and use an index.