Function-playing dimension is one these ideas that’s mentioned lots occasionally. I additionally posted an article about implementing role-playing dimensions in Tabular fashions.

To recap, within the function enjoying dimensions in SSAS Tabular article I defined three completely different options:

1. Importing function enjoying dimensions a number of occasions into the mannequin
2. Creating database views within the supply aspect (in case your supply is a from of RDBMS like SQL Server, Oracle and many others…) then import the information into the mannequin
3. Maintain the inactive relationships within the mannequin and create a number of measures to handle completely different roles utilizing USERELATIONSHIP capabilities in DAX

I this publish I clarify implementation of the third choice above. On this situation it’s good to create especial calculated measures based mostly on the roles you might have in a reality desk. One the commonest role-playing dimensions is Date dimension. Contemplate you need to present Web Gross sales Quantity by Order Date, Due Date and Ship Date in a single chart in your report. On this case, having 3 completely different date tables gained’t assist us to realize the purpose.

New to Energy BI? Shortly find out about Knowledge Visualisation in Energy BI right here.

Mainly, what I’m going to elucidate on this publish is utilizing inactive relationships between FactInternetSales desk and the DimDate dimension by including a brand new Calculated measure. On this case, we’ll be capable of present Gross sales Quantity by completely different roles, properly, dates on this pattern in a single chart.

As a pattern you simply have to import the next tables from AdventureWorksDW2012:

• FactInternetSales
• DimDate

As I’m utilizing one on of my earlier samples that I’ve carried out some tidying up, however, you don’t have to try this. I even have some extra tables, however, once more, to experiment this pattern all you want is to import FactInternetSales and DimDate tables into Energy BI Desktop.

Now I have to create a brand new calculated measure:

• Go to Knowledge view
• Proper click on on Web Gross sales and click on “New Measure”

• You may outline a brand new calculated measure by clicking on “New Measure” from the ribbon

• Sort the next DAX command then enter

Gross sales by Due Date = CALCULATE(SUM(‘Web Gross sales'[Sales Amount]), USERELATIONSHIP(‘Web Gross sales'[DueDateKey],’Date'[DateKey] ) )

• Repeat the earlier step for Order Date and Ship Date as properly to create two different calculated measures within the FactInternetSales desk. Use the next DAX  formulation:

Gross sales by Order Date = CALCULATE(SUM(‘Web Gross sales'[Sales Amount]), USERELATIONSHIP(‘Web Gross sales'[OrderDateKey],’Date'[DateKey] ) )

Gross sales by Ship Date = CALCULATE(SUM(‘Web Gross sales'[Sales Amount]), USERELATIONSHIP(‘Web Gross sales'[ShipDateKey],’Date'[DateKey] ) )

Now scroll down the “Web Gross sales” tables you’ll be able to see all the brand new measures efficiently created.

Now we are able to simply create a report which have all of these calculated measures side-by-side.

• Go to Report view then add a Matrix to the report
• Broaden “Web Gross sales”
• Tick all calculated measures
• Broaden “Date” and tick CalendarYear

• As CalendarYear is integer it robotically goes to Values and it’s good to transfer it to Rows

Now we have now all completely different Gross sales Quantity by Ship Date, Order Date and Due Date.

As you see I used a mix of two DAX capabilities to fulfill our pattern’s necessities. For higher understanding the entire method, it’s good to perceive CALCULATE operate first. Certainly, CALCULATE is likely one of the commonest capabilities utilized in each undertaking entails with DAX formulation. In case your undertaking is concerned with one of many following you’ll more than likely to wish CALCULATE operate:

I don’t need to clarify how CALCULATE operate works as it’s out of scope so I depart it to you to look on the internet and see how CALCULATE capabilities really works.

Typically talking of “USERELATIONSHIP” operate, it’s best to use it in a operate like CALCULATE that takes a filter. The “USERELATIONSHIP” itself does NOT return any values. It really allows a relationship to get used throughout a calculation.

Should you already learn my different weblog publish about Function Enjoying Dimensions it’s possible you’ll ask your self, “we do have all of the roles side-by-side, so do we actually have to import role-playing-dimensions as separate tables in our Energy BI mannequin?

A brief reply is: Effectively, it relies upon! As all the time, it actually is dependent upon your case. This methodology has its personal professionals and cons. Let’s take a look.

Professionals:

1- You should use all of the roles side-by-side in a similar chart as you actually have a separate measure for every function

2- You aren’t importing a number of copies of the roles, as an example, you might have only one Date dimension that can be utilized to slice and cube all of associated measures throughout the entire mannequin

3- It’s extra environment friendly by way of storage and reminiscence consumption

4- Your mannequin is way more tidy while you don’t have a number of roles all around the mannequin

Cons:

1- In giant fashions with numerous completely different roles, creating numerous measures to assist completely different roles can be time consuming and in addition a bit laborious to take care of

2- The measure names are getting lengthy

3- Having numerous completely different measures that look very comparable could be a bit complicated for the top consumer