USERELATIONSHIP – Reveals the power of inactive relationships in DAX!
In the current BI era, dashboards/reports are to appear in a way where the facts and measures need to be shown at various granularities or dimensions in a single report page. There might also be scenarios where we need to visualize the same granularity in different forms.
Scenario:
In a recent Power BI report, we came across a situation where we needed to visualize factual data (monthly goal KPI) in two forms. One being a simple KPI form and the other in the form of a graph filtered by the dimension date range in the axis.
Here comes the tricky part, there were two date fields available in the dimension table. One having the first date of a month named as startdate and the other having the last date of a month named as enddate.
Initially the startdate field of the fact table and the dimension date field were related.
The monthly goal KPI visual was working fine. But when the KPI was filtered by the dimension date field and placed on an axis, it produced a graph where only the first date of the month is included in the graph and not the other dates selected in the date filter.
The actual graph requirement is to have all the dates included in the graph for which it is filtered.
Solution:
Our approach was to write two different calculations in order to meet our requirement.
First of all, we made the relationship between the dimension date field and fact date field to be inactive as highlighted in the below picture.
For calculation A we thought of using the inactive relationship between two date fields as highlighted below.
For calculation B we thought of using a manual date filter (date range) as a parameter passed inside a calculate function and at the same time the date relationship between these two tables should be suppressed.
Here comes DAX which saves the day.
In order to achieve the above calculation considerations, the USERELATIONSHIP DAX function helped us to invoke the inactive relationship between the dimension date field and the fact date field whenever it was needed. In our case it was used in our calculation A and also helped us to suppress the date relationship in our calculation B.
General syntax for USERELATIONSHIP:
Click to Copy
HeadcountKPI =
CALCULATE (
COUNT ( Fact[name] ),
USERELATIONSHIP ( Fact[FactDateKey], DimDate[DateKey] )
)
Calculation B:
datefilter =
FILTER (
Fact,
(
Fact[startdate] < MAX ( DimDate[date] ) && Fact[enddate]> MIN ( DimDate[date] )
)
)
HeadcountbyDateRange =
CALCULATE ( COUNT ( Fact[name] ), datefilter )
Finally, we achieved the requirement by using the above two calculations enclosed within ISFILTERED, another DAX function which helped us to pick the right calculation at the right time.
Other Scenarios:
USERELATIONSHIP can also be helpful in a report where we need to visualize the KPI metrics by two different dates available in the fact table. e.g. Order date and Shipping date.
In general, an active relationship is possible with either one of the fact table date with the dimension table date. In order to make it work, we can make one of the date relationship as inactive and make use of USERELATIONSHIP function with date parameters enclosed within the calculate function.
We can also find some good contents on the usage of the DAX through the following blog page:
Usage of DAX for a specific date:
www.softcrylic.com/blogs/dax-the-hot-date/
