Skip to main content

Posts

Showing posts from April, 2021

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

Fixing Filtering Issues in Power BI

For Power BI data modeling, star schema is recommended by gurus like Marco Russo, Alberto Ferrari all the time. The other day, I have understood the importance of using star schema one more time. You know what they say: Experience is the best teacher. Better than Marco Russo and Alberto Ferrari. Definitely. This mode l looks more like Dalton Brothers than snowflake schema but it's called snowflake schema. Not to be confusing, I removed other dimensions which are not relevant to our subject. As you see there are one to many relationship between all of them and the filtering direction goes from high granularity to low granularity like it's supposed to. In the first table, the Sub-Category column coming from the Products Sub Category table and the Total Sales measure is the sum of the sales column. With All Sub-Category measure, I remove the filter on the Products Sub Category table by using the ALL function. No problem so far. The problem appears in the second table. My All Sub-C...

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