Create slicer to choose the active relationship for Date Dimension

[wmcclure]

Hello all! I have been pondering this for a bit and hope that someone may have some useful tips that I might be overlooking, as always, thanks in advance to the PBI community!

I am trying to create a set of slicers in a report that can dynamically change the active relationship used between an invoices table and a date table. Essentially, I want to have one date search, and a selector for if the invoice has been paid. If someone from the sales team wants to view only paid invoices, I want the relationship to only look at the Paid Date of the invoice.

Alternatively, if a member wants to view active invoices, I want the relationship used to be between the date dimension and the Invoice Date. 

The reason I want to set it up this way is to link to a dynamic title so that the user knows if they are looking at paid invoices, open invoices, or a combination of the two, while avoiding using 3 seperate filters (Paid Date Slicer, Invoice Date Slicer, and "Is Paid" Selection)

In essence,
IF "Invoice is paid" = Yes, THEN Date filter -> Paid Date
IF "Invoice Paid" = No, THEN Date Filter -> Invoice Date

IF "Invoice Paid" = not selected/null (all invoices), THEN Date Filter -> Invoice Date

[amitchandak]

@wmcclure , if you have active, inactive relationship  then you need measures like

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Paid Date], 'Date'[Date]), [Invoice is paid] ="Yes")

+

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Invoice Date], 'Date'[Date]), [Invoice is paid] ="No")

or

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Paid Date], 'Date'[Date]), Filter(Table, [Invoice is paid] ="Yes"))

+

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[Invoice Date], 'Date'[Date]),Filter(Table, [Invoice is paid] ="No"))

With date table, you need to join bith tables :

examples

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or have independent date table with no join

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Invoice Date] >=_min && 'Table'[Date] <=_max && [Invoice is paid] ="No")) + calculate( sum(Table[Value]), filter('Table', 'Table'[Paid Date] >=_min && 'Table'[Date] <=_max && [Invoice is paid] ="Yes"))

[wmcclure]

Thank you, but unfortunately with the number of fields that are being used in the dataset, I dont think calculating each of them with a userelationship filter would be the most adventageous for the sake of time, performance, and upkeep. Do you know of any ways to achieve this type of result outside of the options you suggested? I am experimenting with using a paramater as an alternative but have yet to get much experience with that either.

[tamerj1]

Hi @wmcclure 

by field do you mean measures? How many?

[wmcclure]

I mean fields as in things that are native to the input tables. And there are close to if not over 100, this dataset is responsible for reporting on things like taxes, sales, costs, margins, down payments, etc, all of which are broken down by a number of categories, etc. Creating measures for each of these in order to adjust which relationship is being drawn doesnt seem to be the right solution for our purposes

[tamerj1]

@wmcclure 

Are you using a table visual to display these fields? When you add them to the table, do you summarize them by sum for example or you just display the data as is?

[wmcclure]

Both are used, the same fields used in visuals (breaking down sums by things like location, fiscal year, etc ) as are used for drilldowns for invoice details (shown in a table) 

[tamerj1 · Accepted solution]

@wmcclure 
Appologies for the late response. Actually I had to wait until I have the time to prepare a sample file. This is just and idea which I have implemented before for one of my reports. Can share more details about it if you wish.
In the attached sample file you track the method which basically utilizes a parameter table (manually inserted disconnected table) that contains the names of your measures. Instead of the table visual you need to use a matrix visual, place the measure names field from the parameter table in the columns of the matrix and place all the slicing by columns in the rows of the matrix (you can disable the stepped layout option to obtain the desired appearance). The measure [Final Measure] will be placed in the values of the matrix.

The method can be summarized into two steps

Step1: Create the [Grouping Measure] which is a switch statement that selects the measure to be calculated based on the SELECTEDVALUE of the measure name. This is just the same as the field parameter option in power bi.

Step2: Another switch statement that changes the relationship and hence, the calculation of the selected measure based on the selection of the [Paid?] column in your sales table ("Paid", "Not Paid"). I wasn't sure if you needed this to be part of your sales table so it filters and switches the relationship at the same time or you just needed it to be in a disconnected table to avoid filtering the sales table but only switches the relationship?

Performance implications of this approach are limited however it shall require customization to suit your case.

Please let me know if this is helpful to you or if you need any further clarifications.