Do you want to learn how to address real-world issues with Power BI and DAX? In a recent webinar, Principal Consultant, Paul Turley, teaches you what you need to know. Paul covers many-to-many relationships, using disconnected tables and takes a look at using composite models.
This presentation takes a deep dive into Tabular modeling and calculation and includes demos. Paul’s agenda covers:
Dimensional modeling basics (just a quick run through as this is a more advanced presentation)
Filter propagation in Tabular models
Effects of single and bi-directional relationships as you are creating relationships in your data model
A look at scenarios where data model relationships can or cannot be used to achieve requirements or may or may not solve business problems
Dynamic measures built on a dimensional data model
A demo on DirectQuery in a Gen 1 composite model with Import mode
A first look at new Gen 2 DirectQuery using Power BI datasets which is the next generation of composite model capabilities which is now in public preview
All these topics and demos are based on a business requirement or business problem context, so the presenter will set up a business problem and work through how it can be solved with dimensional modeling.
So, if you’re looking to learn about using Power BI and DAX to address real-world business issues, then this webinar is for you. You can watch the complete webinar below.
I started by building in a CALCULATE/FILTER function in a table to calculate the beginning balance for 2017 for all my assets.
My code (see my video demo for code detail) tells it to calculate the sum of the beginning balance and I filtered the table where fiscal year equals 2017, and finance type equals assets.
Now, let’s say we want to know the assets for every year, not just 2017. To do this, we need to set the year into a variable and then it will calculate that asset for each individual year.
You use the VAR function to set the variable in the code and you’ll need to give it a name. In my case I’ll use YR for year and I’ll have that EQUAL to the fiscal year. It’s important to note that anytime you set a variable, you must hit return at the end.
Next, I’m going to update my FILTER. My code is calculating the sum of the beginning balance and I’m filtering the table where fiscal year equals 2017, but now I want to take that out and change it to fiscal year equals year.
How this works is when this goes through each row it will calculate for each year by using the variable instead of hard coding the year into there.
So previously we only had one outcome for 2017, now when we submit this, we’ll see four outcomes as we have four years’ worth of data, so we’re getting a calculation for our beginning balance for each year.
We can even step this up a bit if we wanted not only the beginning balance for each year, but also for each finance type. Maybe we don’t just want the assets but other values like equity, expense or liability.
All we need to do is set another variable that I’ll call FT for finance type. And instead of doing this for where finance type equals asset, we’ll say where finance type equals our variable.
Now we’ll have the calculation for every year for every individual finance type.
I hope this quick example helps you to start using these DAX formulas in your reports. The CALCULATE/FILTER functions and using variables are something I use all the time in Power BI.
Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected].
We all know Power BI is a powerful analytics tool and it’s easy to create a new Power BI Desktop file, import some data into it and create reports that show valuable insight. Adding Data Analysis Expression, better known as DAX, enables you to get the most out of your data.
If you’re new to Power BI or DAX, DAX is a collection of formulas, or expressions, that are used to calculate and return one or more values. DAX helps you to use data already in your model and create new information from it that can solve real business problems.
When I first started using DAX functions, it brought my Power BI skills to the next level. I was able to tackle some analytical needs that I had struggled with in the past. I’m here to share a couple favorite formulas that I use all the time called the CALCULATE function and the FILTER function. Please be sure to watch my video included in this post as I walk through using this DAX formula.
• In my demo, I’m working with a data set to find the beginning balance for 2017 for our assets.
• To do that I need to sum a column in my table called beginning balance when fiscal year equals 2017 and when financial type equals asset.
• I’ll do this by using a combination of the CALCULATE function and the FILTER function. The CALCULATE function allows you to calculate a function on the entire table.
• In my code I’m going to CALCULATE the sum on our beginning balance. This would calculate the sum for the entire table.
• But we only want to calculate the sum for 2017 for just the assets and financial type. For this, once we have calculated the table, we need filter that table. Think of this FILTER function as making a digital table in the background.
• We need to FILTER it where fiscal year equals 2017 and where finance type equals asset. In my code, I’ll add FILTER for the function, and we need to tell it what table we are going to be filtering, in my case it’s the balance table. Then add where fiscal year equals 2017 and where finance type equals asset.
• Using these DAX functions, our result will show the beginning balance for our assets for 2017.
• My video shows you exactly how to write the code I used here, so be sure to check it out.
As you can see, this is super simple, and this formula allows you flexibility in how you write it. You can FILTER tables in many ways and use different functions within CALULATE. I hope you enjoyed this simple use case of these powerful DAX functions in Power BI.
Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected]
Interested in learning how to set up row level security (RLS) in Power BI when you have varied criteria? In this post I’ll walk through a scenario of setting up dynamic row level security in which your users have unique access needs. In other words, one user has access to the Northeast and Midwest regions, another has access to the Southeast, and two others overlap with access to the South and Northwest regions.
Are you struggling to get past certain limitations while working with Direct Query in Power BI? Today, I want to share a few quick tips to help you go beyond these limitations and maximize your proficiency in this tool.