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?

Answer 1

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!