Measure or column filtered by a relationship and a slicer

Question

I have a table Deals which has columns [DealId], [Open Date Id], [Closed Date Id] where the last 2 columns are like a foreign key to the Date table which has [Date], [DateId] column.

Power BI won't let me have 2 active relationship, so one is inactive.

Now I want to create some visuals indicating the deals that were open and closed in a custom range of time (using slicer).

How I tried to solve

The closest solution to this was creating a calculated column with the LOOKUPVALUE and adding the close and open dates directly to Deals table. And created 2 different pages with 2 different slicers, but this is far not the solution I wanted.

How can I solve this problem?

ZygD · Answer 1 · 2021-03-27T09:59:08.533

1

In this case the easiest way is to implement a role playing dimension functionality by duplicating your date table. Power BI engine does not support role playing dimensions, so the workaround for small tables is just duplicating them, as described in this article.

In your case, you could create a table "Date_for_closed" using

Date_for_closed = ALL('Date')

This creates a copy of your original Date table. Then you can create relationships and only have active ones. This way it is even easier to maintain than a bunch of inactive relationships.

With this implemented you can build this:

from this source:

edited Mar 27 '21 at 09:59

answered Mar 27 '21 at 09:44

ZygD

22,092
39
79
102

Thank you! So, there is no way to have one single Date slicer for the both Closed and Open date id? Maybe there is a way to make one slicer dependent to another, and make one invisible if this is possible? I don't know if this is even possible, since I am new to PowerBi :) – nomel3 Mar 27 '21 at 10:03
This is a good question. I have always used slicers to slice only one field, so what I can tell, I just cannot think of a way to do it right now. Probably it could be possible, but not without some data rearrangements - additional columns, etc. But even in this case, I just don't know now. – ZygD Mar 27 '21 at 10:15

score 1 · Accepted Answer · answered Mar 29 '21 at 18:17

I don't know if what I'm going to say suits your needs based on the size of the tables or the rigidity of the data model due to other measures. I think that in the end what matters is to understand what are the limitations of what you want to show. However, something almost similar I answered here: https://stackoverflow.com/a/66792957/15460989

From what I could understand you have two tables similar to:

Deals = {[DealID] [OpenDate] [CloseDate] [Quantity] [Price] ...}
Dates = {[Date] [MonthName] [MonthNumber] [Year] ...}

And you want to filter Deals based on two relationships

USERELATIONSHIP (Dates [Date], Deals [OpenDate])
USERELATIONSHIP (Dates [Date], Deals [CloseDate])

I am not going to discuss the option of duplicating Dates Table because it was previously covered using two slicers.

But what if the characteristics of my model allow me to use a table with two relationship (One active and the other inactive) while my visualization uses the content of an unrelated table?

Let's define my new unrelated table as:

HarvestingDates = {[Date] [MonthName] [MonthNumber] [Year] ...}

and what I'm trying to achieve is something like this:

From a model like this one:

Deals[DealID]: Unique values.
Deals[OpenDate]: Repeated and missing dates
Deals[CloseDate]: A random number between 0 and 5 is added to Deals [OpenDate]

Instead of choosing an opening date and a closing date, I choose a date range not related to the model and the context related to the deals comes from the measures. Example:

Opened Deals: All the deals opened in a certain date range and summarized by the visualization.

HOpenedDeals: =
CALCULATE(
    COUNTROWS(Deals), 
        TREATAS(
        VALUES(HarvestingDate[Date]),Dates[Date]
        )
)

Closed Deals: All the deals closed in a certain date range and summarized by the visualization.

HClosedDeals:=
CALCULATE(
    COUNTROWS(Deals),
    USERELATIONSHIP(Dates[Date],Deals[CloseDate]),
    TREATAS(VALUES(HarvestingDate[Date]),Dates[Date])
)

Open and closed deals: All open and closed deals in the same date range summarized by the visualization

HOpened&Closed := 
VAR TotalRow= SUMMARIZE(HarvestingDate,HarvestingDate[Year],HarvestingDate[MonthName])
VAR CurrentDates=VALUES(HarvestingDate[Date])
VAR Result=
ADDCOLUMNS(TotalRow, "Count",
VAR CurrentMonthName= {CALCULATE(VALUES(HarvestingDate[MonthName]))}
VAR CurrentYear= {CALCULATE(VALUES(HarvestingDate[Year]))}
RETURN
COUNTROWS(INTERSECT(
                CALCULATETABLE(Deals,
                USERELATIONSHIP(Dates[Date],Deals[CloseDate]),
                TREATAS(CurrentMonthName, Dates[MonthName]),
                TREATAS(CurrentYear, Dates[Year]),
                TREATAS(CurrentDates, Dates[Date])
                ),
                CALCULATETABLE(Deals,
                TREATAS(CurrentMonthName, Dates[MonthName]),
                TREATAS(CurrentYear, Dates[Year]),
                TREATAS(CurrentDates, Dates[Date])
                )
                )))
RETURN SUMX(Result,[Count])

Opened & Not Closed Deals: All open and non-closed deals in the same date range summarized by visualization

HO&NOTC := 
VAR TotalRow= SUMMARIZE(HarvestingDate,HarvestingDate[Year],HarvestingDate[MonthName])
VAR CurrentDates=VALUES(HarvestingDate[Date])
VAR Result=
ADDCOLUMNS(TotalRow, "Count",
VAR CurrentMonthName= {CALCULATE(VALUES(HarvestingDate[MonthName]))}
VAR CurrentYear= {CALCULATE(VALUES(HarvestingDate[Year]))}
RETURN
COUNTROWS(EXCEPT(
                CALCULATETABLE(Deals,
                TREATAS(CurrentMonthName, Dates[MonthName]),
                TREATAS(CurrentYear, Dates[Year]),
                TREATAS(CurrentDates, Dates[Date])
                ),
                CALCULATETABLE(Deals,
                USERELATIONSHIP(Dates[Date],Deals[CloseDate]),
                TREATAS(CurrentMonthName, Dates[MonthName]),
                TREATAS(CurrentYear, Dates[Year]),
                TREATAS(CurrentDates, Dates[Date])
                )
                )))
RETURN SUMX(Result,[Count])

TEST

Date range: {5/27/2021…5/31/2021}

I am sure this can be improved but as I said at the beginning it is just an idea. Cheers!

Measure or column filtered by a relationship and a slicer

2 Answers2