How to create a switch that toggles which relationship to use?

Question

I want to create switch button that will be changing date (By Accounting date or by Effective date) by which report is viewed.

Currently, I have active relationship by Effective date and inactive relationship by Accounting date.

Using USERELATIONSHIP function I am able to bring data by both dates:

Total Premium by AccDate =
    CALCULATE( Sum(Premiums[Premium]),
        USERELATIONSHIP(Premiums[AccountingDate], Dates[Date]))

So my question, how can I place a button (switch) so end user would simply click on that button (whether effective date or accounting date) and all visuals would display data according to the switch?

Do I need to use bookmarks for that? Or there are some other tricks?

Please post some sample data and I will show you an alternative approach, very simple and without using function USERELATIONSHIP. — Przemyslaw Remin, Dec 17 '19 at 14:11

score 9 · Accepted Answer · answered May 25 '18 at 00:36

Let's suppose you've created a new Slicer table to use as a slicer:

Type
----
Accounting Date
Effective Date

Using your two different premium measures,

Total Premium by AccDate =
    CALCULATE( Sum(Premiums[Premium]),
        USERELATIONSHIP(Premiums[AccountingDate], Dates[Date]))

Total Premium by EffDate =
    CALCULATE( Sum(Premiums[Premium]),
        USERELATIONSHIP(Premiums[EffectiveDate], Dates[Date]))

you can create a new measure that switches between these two based on your slicer selection:

Total Premium = 
    SWITCH(SELECTEDVALUE(Slicer[Type]),
           "Accounting Date", [Total Premium by AccDate],
           "Effective Date", [Total Premium by EffDate])

How to create a switch that toggles which relationship to use?

1 Answers1