Forum Discussion
Simple IF over a string returning wrong errors on a table, what's happening?
Hello, i want to create a simple flag like this:
IF (TRIM(SELECTEDVALUE(X[xxx])) <> BLANK() && TRIM(SELECTEDVALUE(X[xxx])) <> "" && TRIM(SELECTEDVALUE(X[xxx])) <> "N/A (abc)",1 , 0)
As you can see, IF is not optimized, because i'm trying to see what's happening. I've removed the variables, added trim, and even considered ""
On a report page with some visuals, this IF "column" is returning wrong values, like :
But if i copy this same table to a new page, it works:
So, what's happening? Could it be any problem with my model?
Regards
HI Ricardo77,
Did you work with the calculated column? If that is the case, they don't respond with the filter and slicers effect so the selectedvalue function may not work properly. (calculate column and filter are work on different data levels, you can use child level to affect its parent)
SELECTEDVALUE function - DAX | Microsoft Docs
I'd like to suggest you direct use the Table[Column Name] to invoke the current row content and compare it with the if statement conditions:
Flag = IF ( TRIM ( X[xxx] ) IN { BLANK (), "", "N/A (abc)" }, 0, 1 )Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxin Sheng
3 Replies
Ricardo77 , hope there is no filter, drill through the filter, slicer filter etc
Also, try like
IF ( not isblank(TRIM(SELECTEDVALUE(X[xxx]))) && TRIM(SELECTEDVALUE(X[xxx])) <> "" && TRIM(SELECTEDVALUE(X[xxx])) <> "N/A (abc)",1 , 0)
Hello amitchandak , thanks for the tip, again it fails on the report page with other visuals, and it's ok in the empty one.
I've found meanwhile that it is something related to the date table, and i believe it should be related to bad dimensional modelling - but open to other ideas 🙂HI Ricardo77,
Did you work with the calculated column? If that is the case, they don't respond with the filter and slicers effect so the selectedvalue function may not work properly. (calculate column and filter are work on different data levels, you can use child level to affect its parent)
SELECTEDVALUE function - DAX | Microsoft Docs
I'd like to suggest you direct use the Table[Column Name] to invoke the current row content and compare it with the if statement conditions:
Flag = IF ( TRIM ( X[xxx] ) IN { BLANK (), "", "N/A (abc)" }, 0, 1 )Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxin Sheng
