Records in table with at least one day in selected period by slicer

[Anonymous]

I have an SQL table with contracts and the valid from (Vanaf Datum) and valid to dates of these contracts. I want to create a table in Power BI in which all records are shown that have at least one day in the selected period by my slicer. Currently, I have the following dashboard:

Now, this table only shows the records which have a Vanaf Datum in the selected slicer period from 1-3-2021 to 31-3-2021. However, I want my Power BI table to include all records (in red) that have at least one day in the selected filter period (in black)

How can I do this in Power BI?

[daxer-almighty · Accepted solution]

Here's the measure to filter the rows:

// If you have a Dates table
// that is DISCONNECTED from
// the fact table...

[Show Contract] =
IF( ISINSCOPE( FT[ContractID] ),
    var ContractStartDate =
        SELECTEDVALUE( FT[Vanaf Datum] )
    var ContractEndDate =
        SELECTEDVALUE( FT[Tot Datum2] )
    var StartDate = MIN( Dates[Vanaf Datum] )
    var EndDate = MAX( Dates[Vanaf Datum] )
    return
        1 * NOT(
            ContractEndDate < StartDate
            ||
            EndDate < ContractStartDate
        )
)

[Radek_Z]

This is marked as a Solution, but I think it is just a partial solution. How would you go about implementing this measure into a slicer? If I try doing that, Power BI won't allow it. It also does not like the "NOT" command, forcing me to remove it and reverse the greater/less than signs (not even sure that will work, but i'm hoping it will).

What is the next step to this solution, so that I can select a period (month) in a slicer and it knows which records to include? Amit's proposal below also doesn't work - PBI doesn't recognize the tables in the CALCULATE command when I try to implement.

[Anonymous]

Great! Exactly what I needed. Thank you very much.

[daxer-almighty]

Secondly, you have to disconnect the Date table from the fact table. Only then will you be able to calculate what you want. All you need is a measure that will return 1 for each row in the table in your first post that should be included and 0 for each that should be excluded. Then you filter the visual using this measure: show a row when the measure = 1. I'll show you the measure in a moment...

[amitchandak]

@Anonymous , Ffor this you need an independent date table and then try a measure like

measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(countrows(Table), filter('Table', ('Table'[Vanaf Datum] <=_max 'Table'[Vanaf Datum] >=Min ) ||('Table'[To Datum2] <=_max 'Table'[To Datum2] >=Min ) ))

[Anonymous]

Can you elaborate on how this measure will help in creating the desired table as I explained above?

[Anonymous]

@Anonymous 

Does your model have a proper date table or do you take your dates straight from the fact table?

[Anonymous]

I have created a separate date table using the DAX expression

Vanaf Datum = CALENDAR(MIN(Contract[Vanaf Datum]),MAX(Contract[Tot Datum])), so the date column ranges from the date the first contract was valid to the date the last contract ends. This table has a one-to-many relation with the Vanaf Datum column in the contracts table and I use this date column in my slicer.

[daxer-almighty]

@Anonymous 

Please have a look at this. It's an excerpt from the documentation of one of the time-intel functions.

In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

• All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.

• There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.

• The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

I don't think your table conforms to this.