Luke Walker on 05 Jun 2018 21:19:29
Set an alternate relationship by report page. Similar to the USERELATIONSHIP () function, but overriding all measures in use on a particular page unless they have a higher priority (e.g., they specify a different USERELATIONSHIP () or filter).
An example application would be having an entire page just for analyzing customer orders by due date and a separate page by order date.
- Comments (1)
RE: page level use relationship
I totally agree. Not having this feature, we need to generate a lot of measures instead, specifically for the relationship we would like to activate. As I understand, the recommendation is to have one single calendar table and one active relationship and additional inactive relationships from the order table.
Ok, we can fix this our-self by writing separate measure for the inactive relationships. Having Meas1, Meas2, etc plus 7-day average, 4 weeks average, etc, the number of mesures start to grow. Also, for the inactive relationships we cant use the built in implicit measures like count, first, average, etc
But this request is not only cause we are lazy. More importantly this will also offload the CPUs. If I'm correctly informed, all fact tables are filtered when filtering the date dimension, even though they are not used in the measures or visualization on the page (this is what I'm been told by the Microsoft support).
Implementing what Luke suggested will make it possible to create a model with a lot of inactive relationships instead of active relationships. The peron creating the report could then choose what relationship to activate, in Lukes example, he will create two equal report pages, but chose different relationships on the individual pages. And you will probably be able to do the same on visual level as well as report level, not just page-level.
For self-service BI, this would be awesome.
I tried design something like this without this feature by creating a Calendar2Fact table having three columns: Date, FactIdFK, RelationshipType. I then inserted this table between the Calendar table and the fact table. I added the Relationship type as a page-level filter so that I could filter on DueDate, OrderDate, etc. But this slows down the measure quite a bit having large fact tables, 50 ms to 500 ms just doing a simple count on a 1 Milion row fact table