Bug Alert! How SELECTEDVALUE and HASONEVALUE Functions Can Upset You?

I've found a bug the other day by chance. When I searched about it, I've found out that it's an unsolved issue for years. If there's an issue that hasn't been solved for years, it definitely deserves a post, even a name. In my opinion, bugs should be named after their discoverers. So here I am, with my bug.

Before I get to the point, I'd like to clarify what the HASONEVALUE function does. Microsoft says: 'Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.' This function is not very intuitive because of its confusing name. Think it more like hasonefilter. It returns TRUE if the column is filtered by only one value.

The measure called time you see below calculates this: If nothing is selected, print "Choose Date" if zero is selected print "Today", if null is selected print "Blank", otherwise extract the selected number as the number of days from today's date.

When I select zero on the slicer, instead of showing today's date, it prints 'Choose Date' like nothing is selected. What's wrong? Why Power BI prints 'Choose Date' like nothing is selected? Thinking maybe there is a problem with some other part of the measure, I've decided to test it solo.

This measure returns true if only one thing is selected, otherwise, it returns false.

Looks like nothing is wrong with anything other than the function itself. This is definitely a bug. And it happens only if I have a blank value in my column. It returns true if I don't have a blank.

Before I get to the problem with SELECTEDVALUE function, I need to explain what SELECTEDVALUE function does. Microsoft says: 'Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns the alternate result.' To put it simply: if I select one thing only, it shows the only thing I selected. If I select many or nothing, it shows the alternate result which I'm going to determine.

This measure is supposed to show the selected value from the slicer if I select only one value. If I select many values or nothing at all. It's supposed to print "Select Day"

The computer says No! Looks like with SELECTEDVALUE function doesn't identify the selection as single if there is a blank in my column. I have tested it without having a blank and again it worked just fine.

This is an issue for almost 4 years and nothing has been done about it so far. Maybe it wasn't reported before and the Power BI team is not aware of it. I don't know if that's the case but I'm definitely going to report it myself.

Comments

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it. This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that. This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website base64-image.de and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...

My Journey with Power BI

Search This Blog