Month: February 2022

Categories
PowerBI

Running totals in PowerBI

When we want to see the evolution of the data through the time and see how it increase in total, we should use Running Totals, a Running Total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence.

Power BI offers us to do it in several ways, the simplest is:

Create a running total with a quick measure

1. Create a new quick measure.

2. In Calculation select Running Total

3) On Base value we must write the value whose running total we want. Secondly, in Field we will write the running field which will be resetting each time this field repeats, for example, a date.

And click the OK button.

4) To see the running total we must add a visualization, like a line chart, with the new created measure.

5) In the Axis we must put the field that we chosen before.

**We have to be very careful because if we put in Axis a field that is not the chosen one, the chart will be complete wrong.

**

6) Our total running would be ready

Another way to do a running total is making by ourselves the measure.

ALLSELECTED: Removes all context filters in the table except filters that have been   applied to the specified columns.

FILTER: Returns a table that represents a subset of another table or expression, the second parameter of this function is important, because it define when the sum will be resetting.

CALCULATE: Evaluates an expression in a context modified by filters.

  • In the all cases we will have to use:
RunningTotal =  
CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
        )
    )
  • There will be times that we are only interested in the calculations being propagated until today, to do that we can use:
RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
        )
    ) 
)
  • If we want additional resetting sum conditions we could add more filters
RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable) && [MoreFilters]
        )
    ) 
)

*For example when we want to reset each year we can use:

RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
	                   && DateTable[Year] == MAX(DateTable[Year])
        )
    ) 
)

ISONORAFTER

Some examples on internet use the function ISONORAFTER that takes a variable number of triples, the first two values are the expressions to be compared, and the third parameter indicates the sort order. The sort order can be ascending or descending.

The first parameter is compared with the second parameter, based on the sort order. If the sort order is ascending the first parameter have to be less than or equal to the second parameter. If the sort order is descending, the second parameter have to be greater than or equal to the first parameter.

ISONORAFTER function returns TRUE if any of the comparisons is TRUE, otherwise returns FALSE.

RunningTotal= 
CALCULATE(
    <BaseValue>,
    FILTER(
        ALLSELECTED([Date]),
        ISONORAFTER([Date], MAX([Date]), DESC)
    )
)

https://www.tutorialspoint.com/dax_functions/dax_isonorafter_function.htm

Categories
PowerBI

Source Path Parameters

A source path is what gives you access to the place where data is stored. This means that when you move your data without uploading the source paths of the tables that referred to it, these tables will no longer be able to find their data, producing an error. 

Similarly, when you find yourself working in groups, there is a chance each member of the group stores data in a different place with different access rights, which will also generate errors for the one member who does not have the credentials and has the data stored elsewhere. 

Another situation is when you are moving a Power BI file from a development to a production environment. The source in the development environment will be different from the source in the production environment. 

To solve those inconveniences there is the possibility of creating parameters that contain a list of the different source paths where data is kept, in a way that each member will only have to select its own source path to be able to access its data.  

The procedure for doing so is explained in a restricted way in the video linked below. (By a “restricted way”, I specifically mean that it is thought for when you are working alone but have two different computers so the source path of the files you extract from your computer will change each time, which implies similar paths)   

https://www.youtube.com/watch?v=ppM-mLckQfs

However, when the paths are not so similar or you are accessing a folder on the net, you have got to make things different than in the tutorial, because data extracted from a web (like SharePoint), is accessed in power BI via its domain, and nothing else, which means you won’t be able to specify the folder or file you want to access when first accessing the data.  

Creating source path parameters

Firstly, we will need to upload the data from wherever it is stored, to give an example I will use data in https://dataxintelligence.sharepoint.com/ sites/AllDataX, specifically accessing the file “DataX Workplan 2022.xlsx”.  

Since the data that we want to access is in a SharePoint Folder, we upload it by using power’s BI option to get Data from a SharePoint Folder. Here you can enter the link to SharePoint as text or as a parameter if you already have it. In this case, we are yet to create our parameter, so we upload with the link as text. 

Remember that the link cannot be the whole URL or power BI will give an error, the provided link must refer only to the domain, without entering any folder or concrete file:  

Once we enter the URL we click on “Transform data” 

And therefore, a new tab in power Query will appear, here is where we can create our parameters and add them to our path before applying any changes to the data. Since the site your colleague gets data from, and the site you get it from, are not similar at all, we will replace the whole source path that is hardcoded in the source of our data (if you want you can let the https:// since that is compulsory, I will add that to the parameter for simplicity) 

In order to do this, we create our first parameter by clicking on the “new parameter” option inside “Manage Parameters” 

Another small window will pop up so that we can customize our parameter at will. Since we are willing to change the source path between ours and the others we select text for the type (links are considered text) and a list of values for suggested values. After selecting this, we fill up the possible values with the different URLs we want to have access to:  

Once we have all the URLs we click OK and we can see our SourcePath parameter in power Query 

Once we have it, we go to our source step in the query we were loading and substitute the link with the parameter  

By doing this we are now able to change the SharePoint site we are getting data from by selecting it in our parameter. This is quite all right, but we are not done yet, we have to access the specific document we want from the site. As said before, for the example I am going to access “DataX Workplan 2022.xlsx”, we do so by applying a filter (in my case I filter so that the column “name” equals the name of my file, but everyone will filter as it suits best for the occasion). After applying the filter, we click on the “Binary” word that is written in the “content” column and power Query will add some steps to allow you to see the file’s content as shown:  

We must be careful with those created intermediate steps because they most likely contain a hardcoded path to the file, which would produce an error each time you changed the parameter because the source path and the path of those steps are not synchronized, one will change but the other will remain the same.  

To solve this, you have to first talk with your colleague and check which folder structure each one of you uses to store the file, with that you can locate which part of the path will have to change so that everyone has access.  

In our example, the path I use to get the file is:  

https://dataxintelligence.sharepoint.com/sites/AllDataX/Documentos compartidos/General/ 

Paying close attention to it we can see that from “https “ to “/AllDataX” we have the text that is selected in the parameter SourcePath we have already created, and the rest of the link is the relative path from AllDataX to the folder that contains the file. My relative path is “/Documentos compartidos/General/” but my colleague´s path can be something completely different like “/Work Docs/General/”, “/Documentos compartidos/Work00001/”, or others increasingly different.  

I will assume that in this case, my colleague’s relative path is “/Documentos compartidos/Trabajo/Horario/” which means that we differ in that where I access “General” they access “Trabajo/Horario”, so that is the part of the path we will create a new parameter for, just in the same way that we created the first one (but with a different name).  

When you have both parameters, it’s time to insert them into the hardcoded path so your query will not break when changing your source, pay close attention to which step you have to modify:  

In our current step we can see our relative path, but it is not really a relative path, it represents the name of the step before the one we are in, which means this is not the path we want to modify. 

 
This previous step is the one that contains the actual path to the file, the one we want to add parameters to. Knowing this, we click on the step and we should find this:  

The path is written after #”Folder Path”=, and we will insert the parameters we created by writing them in as shown:  

You can see that the parameters we added are called SourcePath and RelativePath_forWorkplan, but they can be called as you please, just give them names that will help you remember what those parameters are for. If you have modified this path and clicked enter, and no error warning is showing, congratulations, you are done. 

If you do get an error like the following, please check that the path is correctly spelled:  

A last piece of advice 

You can add these parameters before or after having your queries modified, but I recommend you do it before so that you start with the source connected the right way and do not have to worry about it anymore.