Home > DAX > DAX – USERELATIONSHIP

DAX – USERELATIONSHIP

In this analysis information world everyone wants to see the information in different scenarios, technically role-playing scenarios. Tabular is having role-playing scenarios with a lot of difference from UDM model.
Here we will walk-through a scenario

1. Internet Sales is having 3 different scenarios of dates i.e. relations with Dates such as Order Date, Due Date and Ship Date hence user is expecting to analyse information in 3 different scenarios.


2. Tabular is defined to have one “Active” relationship i.e. in this model defined as OrderDatekey as


3. The design is also having the remaining relations with in-active i.e. these are not under default relations



4. Let’s write a simple scenario using Date and Internet Sales as

EVALUATE(
summarize( 'Internet Sales'
, 'Date'[Calendar Year]
, "InternetSales"
, FORMAT(SUM('Internet Sales'[Sales Amount]),"currency")
)
)



5. Here we could use the “userelationship” to enforce the in-active relations to aggregate the measures such as “DueDate” and “ShipDate”

define 
measure 'Internet Sales'[InternetDueSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[DueDateKey]))

measure 'Internet Sales'[InternetOrderSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[OrderDateKey]))

measure 'Internet Sales'[InternetShipSales] = calculate(sum('Internet Sales'[Sales Amount]) , userelationship('Date'[DateKey],'Internet Sales'[ShipDateKey]))

EVALUATE(
addcolumns( 
values('Date'[Calendar Year]) 
,"InternetDueSales"
, format('Internet Sales'[InternetDueSales],"currency")
, "InternetOrderSales"
, format('Internet Sales'[InternetOrderSales],"currency")
, "InternetShipSales"
, format('Internet Sales'[InternetShipSales],"currency")
)
)



Points to consider:

1. Role playing scenarios are possible in Tabular using DAX functions
2. Developing reports using Tabular needs to have the design information to report developer as well as for ad-hoc reporting
3. Imagine the scenario of end-user developing an ad-hoc report using tabular model without knowing DAX though I haven’t tested this point
4. I can see the importance of DAX before going to use Tabular model

Advertisements
Categories: DAX Tags:
  1. May 1, 2013 at 5:07 am

    Hi there, I would like to subscribe for this web site to
    take hottest updates, therefore where can i do it please help out.

    • May 2, 2013 at 1:06 pm

      Hi Meryl, At present I don’t have subscribe option on this blog but will consider soon

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: