cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Slicer/Filters reflect "Active" data relationship when date filter applied to page/report

When you apply a date based filter using either the "Report" or "Page" level filters, the date value specified is applied to the slicers in the context of the models date "Active" relationship.  This is even the case if all other visuals on the same page/report are reflecting measures that DO NOT use the default "Active" relationship.

 

This behaviour means the slicer will not display possible attributes that should be seen in the context of the secondary date relationship, which the measures reflect.

 

Is there a way to tell Power BI which relationship to use when applying a page or report level filter?

 

This example has 2 date ids in the "Customer" table, both related to the Date dimension table; the first being the customer "JoinedDateID", the second being the customer "LastOrderDateID".  JoinedDateID is the "Active" key for the date relationship.

 

The report page has 2 visuals.  The first is a Slicer based on the customer occupation, the second is a table reporting the 2 count measures, 1 being Customers (using the Active date relationship), the other being Customers (but based on the secondary "LastOrderDateID" date relationship), by the calendar month.

 

Note how no new customers joined after June.

 

If I apply a "Page" level filter to the month attribute and select only "July", the slicer is filtered to show no Occupations.  This is because there were no new customers in July; however, there were 14 customers that there most recent order was in July.  How can I make the slicer respect the second date relationship?

 

9 REPLIES 9
Moderator v-qiuyu-msft
Moderator

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi @Anonymous,

 

I have looked into the report, the measure Customers (Last Order) value 14 because the measure uses UserRelationship() function to use the relationship between the DimCustomer[LastOrderDateID], DimDates[DateID].

 

So what you got is expected. For more information, see: https://msdn.microsoft.com/en-us/query-bi/dax/userelationship-function-dax

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi

 

I think you misunderstand my point.

I want to add a page filter (in the example the calendar month).

When I use this filter for the month of July, the Occupation Slicer, shows no occupations, even though (as you have indicated), there are 14 customers where their last order date was in the month of July.

 

My conclusion is the slicer is only showing occupations for customers that Joined in July, for which there are zero (as the report shows).  This (I beleive), is because the slicer is behaving on the basis of the Active date relationship (joined date), and thus filter all customers (and thus all occupations).

 

I want to tell Power BI which date relationship to use when applying a page/report filter, and a visualisation has no ability to include a measure (which would contextulise the visualisation), and thus the visulisation uses the model Active date relationship.

 

Make sense?

Moderator v-qiuyu-msft
Moderator

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi @Anonymous,

 

Generally, if the page displays column values or measure values which don't use UseRelationship() function, the page level filters will filter data based on the active relationships defined in Manage Relationships. 

 

To use specific relationship, we can use UseRelationship() function in a measure as mentioned previously. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Thanks for the response @v-qiuyu-msft

 

However, I do think you are missing my point.  I know how the UseRelationship works, and I understand the difference between active and non-active relationships.

 

My question relates to the behaviour of Power BI, and specifically on a Slicer visualisation, when you want that slicer visualisation to respond to a date relationship that is not the active relationship. 

 

I can think of many reporting sutuations where the page has only measures that are based on non-active date relationships, where that page is set to only display the measure values for "Current month", or "Current year" etc; unfortunatly, any slicer on that report will only show attributes from the source dimension where the "active" date relationship is being respected.

Moderator v-qiuyu-msft
Moderator

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi @Anonymous,

 

As I understand, when you apply a page level filter with MonthName is July, you would like the slicer visual to display July instead of blank, right? 

 

If it is, it's not supported to do it, the slicer will respect active relationship only. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi @v-qiuyu-msft

 

No @v-qiuyu-msft, that is not what I mean. 

 

When I apply a page (or report filter) based on the date dimension, instead of any slicer visulisation responding to this page/report date based filter on the basis of the "Active" date relationship within the model, I want to be able to specify which of the "many" date relationships I want the slicer to respect.

 

I can achieve such a behaviour with other visuals by applying a calculated measure to this visual that is based on one of the non-active relatiopnships. I cannot add such a context to a slicer.

 

I know I could create a caluclated column in my model, that extracts the relevant non-active related date, and use this caluclated column as my page/report filter; however, such an approach becomes confusing to users of the model, when you have many secondary date relationships, which would require such a calculated column, 1 for each of the non-active date relationships.

 

Does this now make sense.

emilypoon Regular Visitor
Regular Visitor

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Hi all,

 

I am also facing exactly the same problem as explained by the above user.

Can anyone propose a solution for this?

Thanks!

 

Best regards,

Emily

 

nsrshkh1 Member
Member

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

@v-qiuyu-msft 

 

This is the solution I found Alternate Solution

(late though Man Tongue))

You have to modify other Measures that will suit the transformed table DimCustomer1.

 

If this suits your requirement then please mark this as Solution so that others can refer this.

 

Thanks

jch2s Frequent Visitor
Frequent Visitor

Re: Slicer/Filters reflect "Active" data relationship when date filter applied to page/rep

Is there a working link for this solution?

 

Regards

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)