Power Apps – Page 4 – STL

Microsoft has created some interesting AI (Artificial Intelligence) visuals for Power BI. Power BI offers four AI visuals – Q&A, Key Influencers, Decomposition Tree, and Smart Narrative. This is Power BI AI Visuals Part 2 – Key Influencers.

AI visuals are extremely useful and all four can provide your report with insight information from your data model. Without these, you have to do DAX measures and create your own visuals.

Key Influencers visual

We used the same HR data model in the entire four-part series ‘Artificial Intelligence Visuals in Power BI Desktop’. In this blog post, we explain the Key Influencers visual. The HR data model measures the employees’ engagement score, number of sick days, and the employees’ productivity.

The report needs to visualise the key influencers for high engagement, high productivity, and why someone has many sick days.

To do this, some decisions have to be made. You will have to define what a high engagement score is, what a high number of sick days is, and what a high productivity score is.

The Method

If you decide that more than 5 days a year is a high number of sick days, you will need a calculated column where you test the number of days an employee has been sick. The calculated column could look like this: High/Low # of days sick=If([Days of sickness]>5, “high”,”Low”). We need to use the same logic for engagement and productivity.

To get the Key Influencers visual, click Key Influencers on the Insert tab in the AI visuals group.

The Results

In the example below, we added the logical column to the Analyse field in the visual. At the top, we selected High, because the key influencers for many days of sickness need to be found.

Below, we have dragged the information about the employees’ gender to Explain By. As you can see, the employees’ gender is not a key influencer for high level of sickness.

Below we have added four more columns to Explain By: Department, Part/Full Time, Title, and Hire Date. The Key Influencers visual now explains that the top influencer for a high number of days sick is Title. If you are a general employee in this company, the chances of having a high number of sick days are 6.2 times higher than other titles. The second largest influencer is an interesting one. It is Hire Date. All employees hired from the 22 January 2015 to 6 August 2015 have a 3.1 times greater chance to have more sick days.

Below, High has been changed to Low to show the key influencers for a low number of sick days.

Within the Key Influencers visual, each key influencer can be visualised with a chart inside the visual. Above, you can see the chart for the top influencer. If you want a key influencer visualised in the chart, you just need to click on the circle with the number.

Below, we clicked on the circle with the number 1.32x to see the chart for Part/Full time

Below, you can see the visual showing the key influencers for Low productivity.

The last image shows the key influencers for a high engagement score.

Conclusion

The Key Influencers visual can give you very surprising insights from your data model. You can cross-analyse many data sources. This will give you an understanding of correlations and variables which influence your company’s results negatively and positively. Such insights can help you make decisions which will increase profitability. The Key Influencers visual can make in-depth analysis of your data sets much quicker.

This concludes Power BI AI Visuals Part 2 – Key Influencers. If you want to learn about the remaining two AI visuals in Power BI, please follow STL on LinkedIn or visit our website.

STL has two Power BI courses which include AI visuals. Power BI Reporting and Power BI Modelling, Visualisation and Publishing.

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic at times. Especially one piece, the Calculate function, can be challenging.

This is the fourth part of a series of blog posts, which will investigate the mysterious Calculate function.

The Calculate function react differently to row, column, filter, and table context than the other DAX functions and can be used with date intelligence and can handle many other special requirements.

Trick the data model to Pass Filters from Many to One Side of Relationship

In this blog post you will see how the Calculate function can trick the data model to Pass Filters from Many to One Side of Relationship.

In a report the number of cities you have sold to need to be visualised.

A DistinctCount function can count distinct entries. However, as you can see below, the DistinctCount function cannot filter the result by month. It just returns 49, which are the total number of cities sold to.

The reason that the DistinctCount function cannot filter it by month is clear in the data model.

It goes against the filter direction in the relationship between the Orders and the Customers table.

The Dates table is related to the Orders table, and the filter direction is going from the Dates table to the Orders table, but the filter direction in the relationship between Orders to the Customers table is going from the Customer table to the Orders table. This means that the Dates table cannot filter the cities.

Distinct Shipping Methods

The DistinctCount function can show the number of distinct shipping methods used each month. This is because the relationship’s filter direction between the Dates and Orders table is going from the Dates table to the Orders table.

The task was to count how many different cities sold to each month. So how are you going to achieve this?

The mysterious Calculate function can handle this, as it can handle a lot of other special tasks.

The DAX measure will look like this:

Distinct Cities = CALCULATE(DISTINCTCOUNT(Customers[City]),orders)

By nesting the DistinctCount function inside the Calculate function and adding the Orders table to a filter argument in the Calculate function, you now can pass filters from Many to One side of Relationship (go against the filter direction). You will need to enter the Calculate function into the expression to reference the table you want the expression from as well as in the filter argument the table, which are related to the table in the expression.

You can see above that now the measure returns the right result.

Distinct Products Sold

If you need to visualise the number of distinct products sold each month, you can use a similar approach.

Again, you will have to go against the filter direction (Pass Filters from Many to One Side of Relationship). Here the relationship between the Items table and the Line Items table.

Conclusion

I call the Calculate function the mother of all DAX functions. It is the most important DAX function (my personal opinion). But you need to understand how it reacts to row, column, filter, and table context, and can be used to handle different issues. As a result, you will get the best out of it. Concluding in this blog which has shown how it can pass filters from Many to One Side of Relationship.

This is part 4 of a series of blog posts as mentioned at the top about the mysterious Calculate function in Power BI Dax.

Blog Categories