Forum Discussion
LOOKUPVALUE blank rows
Hello
Does anyone know why I don't search in all the ranks the same?
I use LOOKUPVALUE and some find them and some don't.
Thank you.
8 Replies
Check the values in the representante column to make sure that they are all exactly the same and don't have any whitespace characters.
If it is already verified, this data also comes from a sql query. It's like very random.
All I can suggest is the steps that I would take to investigate the problem.
First, create a one-to-many relationship from the vendors table to the fact table. It might be necessary to do this in a new test PBIX if the production model won't allow a relationship due to ambiguity or some other issue.
Add a new column to the fact table which is RELATED(vendors[name]). Compare the results of this column for rows where the LOOKUPVALUE is returning blank.
Using DAX Studio, View Metrics and look for RI violations on the relationship between vendors and fact.
You could also use DAX Studio to perform manual LOOKUPVALUE calls with different text strings.
I have a clue that can help because it is still eye-catching. I have reviewed the entire list and all the ones that I am missing (which are blank although it should have value), are those that the column "month" is 3. ALL.
Not sure that that helps, as in the screenshot you posted there is a row from month 3 which does have a value. I think that the problem will ultimately have to do with the representate column.
Do you have any values in rows for month 4? Just wondering if something changed in the source system part way through March. But you have a value missing on March 13 then a value correctly appearing on 21 March, then it disappears again on 31 March, so that might be a red herring.
Yes, it is something very random it seems. Because, is there any other option that can replace LOOKUPVALUE?
You could use
Represent. = VAR Representante = His_cab_fac_venta_sql[representante] VAR Result = MAXX ( FILTER ( Vendedores_sql, Vendedores_sql[Code] = Representante ), Vendedores_sql[Name] ) RETURN ResultIt would not be as efficient, but as this is a calculated column not a measure that doesn't matter as much.
Perfect, ok function. Well the other will remain unknown at the moment.
Thank you very much for everything.
Best regards
