I was sitting here in my home thinking what to write for my next article on my blog. I thought about what could be most helpful for my readers. When I was in my initial learning days I found that DAX is the most important language in PowerBI. With the help of DAX, you can get the most out of the PowerBI.

In this article, I will be helping you by providing the best resources to learn DAX. Well, Best Choice varies from person to person but still, there are always a few important resources that can not be neglected.


If you ask me, What are the best resources to learn DAX?

  • Introduction to DAX Video Course
  • The Definitive Guide to DAX
  • DAX Fridays
  • Microsoft Power BI Community

Introduction to DAX Video Course - SQLBI




The Introduction to the DAX Video Course is provided by SQLBI. SQLBI is run by Marco Russo and Alberto Ferrari. They have expertise is DAX and providing a lot of valuable content on www.sqlbi.com.

The Introduction to DAX Video Course is free of Cost and very valuable for beginners. You can get the access to DAX Video Course by clicking here.

They also offer a lot of paid Video Courses as well so if you are interested you can check out SQLBI. You will not regret it even if you pay for their courses.

Once you get hold of DAX and want to learn further, You can check out Mastering DAX Video Course.


The Definitive Guide to DAX



If you ask me what is the ultimate resource to learn DAX then I will recommend this book. This book is amazing and extremely helpful. Every topic is explained in this book is explained with the help of the example so that it's easy to understand.
The Definitive Guide to DAX is written by Marco Russo and Alberto Ferrari who are also founder of SQLBI.

I recommend you to learn the context transition topic from this book as it is explained very beautifully in this book.

You can get this book on Amazon by clicking on the link.



DAX Fridays



Curbal has been one of my favorites Youtube Channel for learning Microsoft Power BI. DAX Fridays is an initiative from Curbal Youtube Channel by posting videos every Friday explaining the DAX Function with the help of Example.

Now, almost every DAX function has been explained by her. You can check out her videos as they are very useful and interesting.

Curbal Youtube Channel has videos all around PowerBI. You can learn Data Modelling, M Query, and Power BI Functionality in her Channel.


Microsoft Power BI Community




You must be wondering how does Microsoft Power BI Community helps us in learning DAX. Does it provide any articles related to DAX? 

The answer is NO. 

An important aspect of learning is not only to read books or articles. The most important thing is to practice. If you don`t practice then your learning will be useless and you will soon forget everything. 

So, You must be thinking that Where can I practice DAX after reading all the articles and watching all the Videos?

You can practice DAX while working on a report in your project but sometimes the scenario which we use in reports is very straight forward, easy and we are not able to use all the formulas which we have learned through our reading.

For Example, Let suppose you are working on a  report where you have to find some count of the record later filter it based on some dimension column and you are getting almost similar kind of scenario in your project than what will you do? Will, you just wait and time will pass and you will not able to learn anything.


The Best way to practice DAX is to go to Microsoft PowerBI Community which is an excellent place to help people with DAX and other Power BI-related problems and increase our learning. There are various forums where we can go and find the problem asked by people from all over the world.

Below are some of the Categories in Microsoft Power BI Community

  • Desktop
  • Service
  • Report Server
  • Power Query
  • Mobile Apps
  • Developer
  • DAX Commands and Tips

You can go directly to DAX Commands and Tips Section and find appropriate Question which you can answer and help other fellow Power BI developers.

In fact, I have recently started a DIY article series on my blog where I solve a problem using DAX in a step by step manner so that reader can follow along with me and practice on his/her own. I have learned a lot by helping people in this section in Microsoft Power BI Community.



How to format the DAX Code?



Dax Formatter is an online tool provided by SQLBI which is very useful and beautifies your dax code in an elegant manner. Formatting DAX code is extremely important for easy reading and as per best practices.




Using DAX FORMATTER is very easy, You just have to copy your DAX Code from Power BI and paster it into the space provided and just by pressing in FORMAT button. Your nonaligned dax code gets aligned properly.

Later, You can copy it and paste it in Power BI in order to maintain best practices. Even it provides HTML code so that you can copy DAX code in HTML Web Pages.



I hope the article provided value and helped you in your journey to learn DAX and Power BI. You can comment and share the article with your friends.







Best Resources to learn DAX


DIY4 :  Learn to use LOOKUPVALUE DAX Function in Power BI using Example


I was solving a problem on Microsoft Power BI Community which proved to be the best use case for LOOKUPVALUE function in PowerBI. So finally, I decided that I will write a blog about it so that my readers understand where and how to use the LOOKUP VALUE Function in PowerBI.


What is the LOOKUPVALUE function in DAX Language? LOOKUPVALUE function is a very useful function in DAX as it helps to find the value of a column based on another column value in a table. You can easily relate it to the LOOKUP function in Excel.

Read more:  LOOKUPVALUE  Function Microsoft Docs



Let`s learn LOOKUPVALUE function in DAX language with the help of an example. The Whole Purpose of writing this article is to help readers understand the function and practice along with me so that they get along with me and get hands-on experience of using LOOKUPVALUE function.



Let`s Begin.


Problem Statement 



DIY4 :  Learn to use LOOKUPVALUE DAX Function in Power BI using Example


Qtr column has to be placed in the slicer  and if the user selects any value from the slicer then that value and its previous value should be shown in the Card.

If nothing is selected in the slicer then maximum Qtr value and its previous value should be shown in the Card.





Result Preview

When Nothing is selected in the Slicer then maximum Value and its previous value are shown in the Card.
DIY4 :  Learn to use LOOKUPVALUE DAX Function in Power BI using Example

When any value is selected in the Slicer then that value and its previous value is shown in the Card.


DIY4 :  Learn to use LOOKUPVALUE DAX Function in Power BI using Example

Hope the requirement is clear to all. Its a very good use case for using LOOKUPVALUE function.

So let`s see the solution to the problem but I would request you to please try to solve the scenario by yourself first, if you find it difficult then you can see the solution here.

Solution

First , We have to create measure to capture previous Qtr value based on selected value in the slicer. In order to achieve this we have to use LOOKUPVALUE function so that we can lookup the Qtr column with the help of Index Column.

Please find below Measure DAX Expression for capturing previous value when Qtr value is selected in the slicer.


Previous_Qtr =

    LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) - 1 )


Here , In the above expression, When Qtr column is filtered, LOOKUPVALUE function will be evaluated in DAX. LOOKUPVALUE function will return the Qtr column value for the previous Index than the maximum index in the Index Column. Since Qtr is filtered using Slicer then maximum  Index Column value will also get filtered.


Now We will write the main DAX Expression,


select_measure =
IF (
    ISFILTERED ( 'Table'[Qtr] ),
    MAX ( 'Table'[Qtr] ) & " & " & [Previous_Qtr],
    LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) ) & " & "
        & [Previous_Qtr]
)

In the above DAX Expression, If the slicer is filtered then

MAX ( 'Table'[Qtr] ) & " & " & [Previous_Qtr]

expression will be evaluated, so after filtration only one row will left in the table MAX ( 'Table'[Qtr] ) will capture the selected Qtr Column value and previous Qtr Column value will come from measure [Previous_Qtr].

When nothing is selected in the Slicer then LOOKUPVALUE ( 'Table'[Qtr], 'Table'[Index], MAX ( 'Table'[Index] ) )  expression will give maximum Qtr Column value and [Previous_Qtr] will give the previous Qtr value.

MAX ( 'Table'[Index] ) will be changing based on condition and the LOOKUPVALUE function will fetch us the Qtr Column value accordingly.



Conclusion

We learned about the LOOKUPVALUE function with the help of a Practical Scenario. I Hope, it was useful to all the people trying to learn PowerBI and DAX.


If you like this article then please share and comment. You can also provide your feedback to me.


Cheers!!


DIY4 : Learn to use LOOKUPVALUE DAX Function in Power BI using Example


Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total


Again while solving the problem on Microsoft PowerBI Community, I got a problem related to the Conditional Cumulative Sum/Running Total so I thought to put that problem along with a solution for my readers here. You can practice it and gain a lot of valuable experience in Power BI.

Well, I am running Microsoft PowerBI DIY Series on the blog, you can subscribe to my blog to get updated content on your mail. Check out the last article below.



Read More:


What Do mean Conditional Cumulative Sum / Running Total in PowerBI? Conditional Cumulative Sum/Running Total means we have to add and subtract the running total next entry based on the basis of particular column value which acts as a condition here.



Problem Statement

When Transaction Type is Buy then we have to do running total/ Cumulative Sum of transaction units and when transaction type is Sell then we have to subtract the transaction units from the cumulative sum taken previously.

Transaction DateTransaction TypeTransacted unitsStock NameCumulative figure till date
1.1.2020Buy100A100
2.1.2020Buy50A150
5.1.2020Sell20A130



Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total


Now let`s see the result table which we want to make -


Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total


we have to make calculated Column Commulative_Figure_Till_Date.



Solution

First, we have to make the Calculated Column with name Commulative_Figure_Till_Date  as shown in the figure above.

Step 1: Make the Calculated Column

To make Calculated Column, Click on the New Column Button in the Home Tab.

Microsoft Power BI Desktop Exercise - 3 | Conditional Cumulative  Sum / Running Total


Step 2: Write DAX Expression for getting the desired result

Please try to write the DAX Expression first on your own and if you are unable to write correct DAX then you can see the DAX expression below and understand the logic used in the Expression.


Commulative_Figure_Till_Date =
VAR TransactionDate = 'Table'[Transaction Date]
VAR Comm_sum =
    CALCULATE (
        SUM ( 'Table'[Transacted units] ),
        FILTER (
            'Table',
            'Table'[Transaction Date] <= TransactionDate
                && 'Table'[Transaction Type] = "Buy"
        )
    )
RETURN
    IF (
        'Table'[Transaction Type] = "Buy",
        Comm_sum,
        Comm_sum
            CALCULATE (
                SUM ( 'Table'[Transacted units] ),
                'Table'[Transaction Date] = TransactionDate
            )
    )



Calculated Column creates an Outer Row Context by default so Variable TransactionDate is iterating the value of Transaction Date Column One by One.

FILTER in the Variable Comm_sum is providing inner  Row Context and Transaction Date Column iterate completely so each iteration provided by outer row context of the calculated column so the sum of Transacted units take place of each date less and equal to Current Row Context Date.

Let's understand the Variable  Comm_sum, Here Sum of Transacted Units is taking place for All dates which are less than and equal to Dates coming from outer row context.

Let's suppose the outer row context has value 2/1/2020 so the sum of transacted Units will take place for 1/1/2020 and 2/1/2020. But we need to do this only for records which have transaction type as Buy. That's why we have used AND condition && 'Table'[Transaction Type] = "Buy".

AND condition help to do not evaluate the third record because if it evaluates the third record then 150 Cumulative sums will get added to 20 from the third record and would become 170 and then if we use our final IF condition then it will subtract 20  and give us 150 as a result which is incorrect so that`s why  && 'Table'[Transaction Type] = "Buy" condition has been used in the Code.

IF Condition used in the return statement will do Cumulative Sum for records having transaction type as Buy while for records having transaction type as Sell. 

Comm_sum
            CALCULATE (
                SUM ( 'Table'[Transacted units] ),
                'Table'[Transaction Date] = TransactionDate
            )

the expression is evaluated. Thus Sell Record Transacted  Unit is subtracted from Cumulative Sum coming from previous records.

I know the above lines seem too confusing you but that is the most important element of DAX which we need to understand.

To better understand the Row Context and Filter Context, I highly recommend reading book
"Definitive Guide to DAX". No book is better than this book.

You can order it from below link -




Conclusion

I hope you find Conditional Cumulative  Sum / Running Total article valuable in your learning Power BI Journey. Go through all the articles and also let me know anything you did not understand in the above article, I will be happy to help you.

Do Share and Like if you find the article useful.


Thank You.

  
Read More: DIY 2: How to achieve Relative Date filtering in Power BI




DIY 3 : Learn to achieve Conditional Running Total in PowerBI using Example

Microsoft Power BI Exercise-2 | Working With Relative Dates

In this article, let's prepare a demo for Relative Date using DAX Expression in PowerBI. Relative Date means When you select a date in Slicer then last n days data get analyzed with respect to any measure. For more details, read the article below.


I got a ping from a friend that he wants only the last 30 days of data in all the visuals on the page based on the date selected in the slicer.

This requirement is very common among clients because in order to see the trend for the last particular number of days we need to use relative dates in Power BI.

Here I have written the article in a step by step manner so if someone wants to practice he/she can practice it very easily by themself.


Step 1: Create a Data Table using DAX Expression.

Here, We need two tables for example here.


1. Data Table

Using the below DAX Expression you can create a data table.


Data Table =
ADDCOLUMNS (
    GENERATE (
        CALENDAR ( "2012-01-01"TODAY () ),
        SELECTCOLUMNS ( GENERATESERIES ( 12 )"Units Sold"RANDBETWEEN ( 15 ) )
    ),
    "Month"DATE ( YEAR ( [Date] )MONTH ( [Date] )1 )
)

2. Dates Table

We also require the date table here for this requirement. We can create Date Table using the below mentioned DAX Expression


Dates =
ADDCOLUMNS (
    ADDCOLUMNS (
        CALENDAR ( "2016-01-01"TODAY () ),
        "Month"DATE ( YEAR ( [Date] )MONTH ( [Date] )1 )
    ),
    "Month Inverse Sort Col", - INT ( [Month] )
)


Step 2: Data Modelling


Microsoft Power BI Exercise-2 | Working With Relative Dates

Here, Dates Table has been kept isolated so that it does not filter the main data table. Values selected in the slicer are captured in the Dax Expression which is discussed later in the article.


Step 3: Creating Visuals in Power BI Desktop


Microsoft Power BI Exercise-2 | Working With Relative Dates


Here, I have created the Line Graph with Units Sold on the Y-axis and Date on the X-axis. Along with it, I have created a slicer having a Date in it.

These visuals are just for example. In your actual project, there can be any number of visuals having the requirement to show only relative dates.


Step 4: Relative Date Flag Creation

The flag can be created either by making calculated columns or by making calculated measures. The best way is to use calculated measure since it does the calculation on the fly. 

We will be using the Date column present in both Data Table and Dates Table. Please find below Dax expression used for creating a flag.


Relative_date =
VAR Data_date =
    MIN ( 'Data Table'[Date] )
VAR Days = 30
VAR Datetable_date =
    MAX ( Dates[Date] )
RETURN
    IF ( Data_date < Datetable_date && Data_date >= Datetable_date - Days10 )


Here Data_date is the variable that is taking value which is selected in the slicer. Datetable_date is another variable that is taking value from the Dates table. For all the values which are less than the selected date but greater than selected date -30 days, the flag is set as 1.


Step 5: Placing the Flag in the Page Visual Level Filter

Microsoft Power BI Exercise-2 | Working With Relative Dates

Now you have to place the Measure Relative_date in the visual level filter with its value as 1. After placing it you will get only those dates for which is less than the selected date in the slicer.

If you like the article, Please share the blog and comment in case of any feedback.


Thanks.







DIY 2 : How to achieve Relative Date filtering in Power BI

DIY 1 : How to do  Conditional Formatting using field value  in Power BI


Here, In the article, I am going to share a very general use case i.e to show a message that slicer is not selected on the top of a visual if nothing is selected in the slicer. Showing and Hiding Visuals such as Table/Gauge/Matrix is generally used in the Power BI report.

In order to achieve this, conditional formatting using the field value options can be of great use.

How to do conditional formatting using field value in Power BI? Conditional formatting using field value is a setting in PowerBI through which you can format the Title Font color, background color, Border Color, etc. using DAX Expression which is generally Conditional SWITCH or IF function for setting the color for each of the condition.


Problem Statement

I have a table having values of sales based on occupation. Also, I have a slicer that is filtering the main table.


Microsoft Power BI Exercise | Show Message If Slicer not Selected | Matrix/Chart/Table Conditional Formatting


The requirement is to display a message "You must first select a Product Category from the slicer to see the results" when nothing is selected in the slicer.


Microsoft Power BI Exercise | Show Message If Slicer not Selected | Matrix/Chart/Table Conditional Formatting




Solution


In order to achieve the above-mentioned requirement, We have to place a card on the top of the main table and create a Message measure in order to show the message in the card when nothing is selected in the slicer.


So Dax expression for the Message Measure is mentioned below -


Message =
IF (
    ISFILTERED ( Products[Category] ),
    "",
    "You must first select a Product Category from the slicer to see the results"
)



As per the dax code if anything is selected in the slicer Category then the card will not have any message but when nothing is selected in the slicer then message "You must first select a Product Category from the slicer to see the results" will get displayed.

One more thing which needs to be done is to handle the background of the card for which we will be using conditional formatting using the field value option in powerbi. 

Handling the background of Card using Conditional Formatting


In order to achieve this, we have a create a measure Make Transparent. The Dax expression for the mentioned measure can be found below.

Make Transparent =
IF ( ISFILTERED ( Products[Category] )"#FFFFFF00""White" )



Here, using this code we are trying to make the background transparent when any value is selected in the slicer and if nothing is selected in the slicer then the background will be white.


Ultimately our goal is to hide the table when nothing is selected in the slicer and display the message in the card. Also, if anything is selected then the table is visible and the message is not visible.


We are achieving this using the above mentioned 2 measure. Let`s understand this.


Condition 1 - Value selected in the Slicer

When any value is selected in the slicer then nothing will be displayed in the card which is taken care of by Message measure at the same time Make Transparent measure make sure that background is transparent so that the main table is visible which is placed below the card.

Condition 2 - No Value selected in the Slicer 

When nothing is selected in the slicer then Message Measure will display the message in the card and Make Transparent measure will set the background as white so that the main table is not visible placed below the card.

Now You must be thinking of where to place these 2 measures mentioned above the article.


Message Measure should be placed in the values option of the Card.


Microsoft Power BI Exercise | Show Message If Slicer not Selected | Matrix/Chart/Table Conditional Formatting



Make Transparent Measure is written in order to set the background using conditional formatting using the field value option. 

So Click on the  Card and go to Format option Visualization Pane, Click on the background three dots button on the right.


Microsoft Power BI Exercise | Show Message If Slicer not Selected | Matrix/Chart/Table Conditional Formatting



Once you click on it, you will get a screen that has options for conditional formatting.

1. Color Scale
2. Rules
3. Field Value


We will select Field Value because we want to do conditional formatting using Dax expression. Once you select option Field Value, there is a dropdown to select an appropriate measure from the tables.
Just select here the measure Make Transparent which we have already discussed above in the article.

Microsoft Power BI Exercise | Show Message If Slicer not Selected | Matrix/Chart/Table Conditional Formatting



I hope that the article will help the upcoming developers to understand the concept of conditional formatting based on field value options in Power BI.


If you like the article, please comment below and share your feedback. I will come later with another practical application article soon.


Cheers!


Read More: Virtual Relationship in Dax | Demo with TREATAS Function

DIY 1 : How to do Conditional Formatting using field value in Power BI


Virtual Relationship in Dax | Demo with TREATAS Function


Here, I will be discussing a very important dax function TREATAS which can be used to establish a virtual relationship between tables. I will provide steps that will help you to master this function.

Virtual Relationships are a very important concept in Dax. Whenever it is difficult to establish a relationship between two tables. A function such as TREATAS comes to the rescue and helps to establish the relationship.

Sample Data Model



Virtual Relationship in Dax | Demo with TREATAS Function


Here, In the Data Model, We do not have any physical relationship between People and Orders but we want to find Sales by Person. So, Sales we have to get from Orders Table and Person is in the People Table.


Without any Relationship between Table


So, Without any relationship between the table if we try to place Sales and Person in a Table Visual. Let`s see what happens.


For each person, we are getting the total sum of Sales. It means Sales is not filtered by Person since there is no physical relationship between Orders and People Table.


With Virtual Relationship using TREAT AS Function


Now, Let`s try to establish a virtual relationship.

Step 1 -  Create a measure Salesbyperson and use Dax statement.


Salesbyperson =
CALCULATE (
    SUM ( Orders[Sales] ),
    TREATAS ( SUMMARIZE ( People, People[Region] ), Orders[Region] )
)

Step 2 - Use the Salesbyperson measure and see the magic. 


Virtual Relationship in Dax | Demo with TREATAS Function
Now, we can see that Sales data from Orders Table is getting filtered by Person coming from People Table. 


Here, In the mentioned data model, we could have established a physical relationship between Orders and People table but there are some scenarios where it is difficult to establish relationships with a physical relationship and there is a need to establish a virtual relationship. At that point in time TREATAS function comes into the picture.

If you are interested to get the PBIX file for this demo, you can comment below with your email id. I will send you the PBIX file.


Thank You.


Virtual Relationship in Dax | Demo with TREATAS Function