# DAX

## Tabular Modeling & Calculation Techniques Beyond the Basics in Power BI

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.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Tabular Modeling & Calculation Techniques Beyond the Basics in Power BI

## Setting Variables Using DAX in Power BI

Welcome to another Azure Every Day focused on one of my favorite topics, using DAX in Power BI. In a previous post, I covered a couple of my favorite DAX formulas, CALCULATE and FILTER. Here, I’ll discuss variables and how to work those into CALCULATE/FILTER to expand on that and make it even more powerful. You may want to check out my previous post here, as I’ll be continuing using that demo.

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

Setting Variables Using DAX in Power BI

## DAX CALCULATE and FILTER Functions in Power BI

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.

DAX CALCULATE and FILTER Functions in Power BI

## How to Test Dynamic Row-Level Security in Power BI

Want to learn how to test dynamic row-level security in a Power BI Desktop file? In a previous post, I showed how to set up dynamic row level security.

How to Test Dynamic Row-Level Security in Power BI

## Dynamic Row Level Security in Power BI

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.

Dynamic Row Level Security in Power BI

## Direct Query Limitations in Power BI

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.

Direct Query Limitations in Power BI
{"ft_lang_label":"__label__en","ft_lang_prob":0.90796936,"math_prob":0.72370535,"size":6917,"snap":"2021-31-2021-39","text_gpt3_token_len":1460,"char_repetition_ratio":0.118183136,"word_repetition_ratio":0.07648953,"special_character_ratio":0.2103513,"punctuation_ratio":0.075636365,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96407586,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-09-23T14:02:22Z\",\"WARC-Record-ID\":\"<urn:uuid:3ec97c35-157f-4b82-a6d4-55b6fc0944b9>\",\"Content-Length\":\"64429\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:8b804d4b-bff4-4b28-bbf9-0275db7256fd>\",\"WARC-Concurrent-To\":\"<urn:uuid:5b7a8913-4184-42d5-a258-5b9f591a9b08>\",\"WARC-IP-Address\":\"104.21.36.90\",\"WARC-Target-URI\":\"https://www.3cloudsolutions.com/category/dax/\",\"WARC-Payload-Digest\":\"sha1:GCUZGJYDWSRTW3GHVEC3X7UAKJ4U66J7\",\"WARC-Block-Digest\":\"sha1:43J4DO6HI75GRDCQ6BONVJOGHVSQZYEA\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-39/CC-MAIN-2021-39_segments_1631780057424.99_warc_CC-MAIN-20210923135058-20210923165058-00218.warc.gz\"}"}