Measure by Slicers data Selected and Filter DATE between two Fields that have two Relationships

[Hernán]

Hi ,

I hope you are doing great.

I need to create a Measure or any solution similar for to have in a Card the next calculation:

The Selected DATE taken from two Slicers Year, Month must be less and Filter another Field where the values are blanks or the values are greater than Selected date in the slicers.

Id_Before_Date_Selected =

/* I need to DISTINCTCOUNT(Id) where Field Date1 is less than Selected in Slicers Year and Month && Date2 is Blank || Date2 is greater than Date Selected in the Slicers (Year and Month). But,

The relationship is Cal[Date] and tb_employees[Date1].

The relationship Cald[Date] and tb_employees[Date2] is inactived.

*/

// In the next Measure the error is that field: tb_employees[Date2] is taken the data from tb_employees[Date1] (relationship active) but must be Date2 and I can't desactive that main relationship.

VAR SelectedYear = SELECTEDVALUE(Cal[Year])

VAR SelectedMonth = COALESCE(SELECTEDVALUE(Cal[Month]), 1)

VAR SelectedDate = DATE(SelectedYear, SelectedMonth, 1)

RETURN

    CALCULATE(

        DISTINCTCOUNT(tb_employees[id]),

        FILTER(

            ALL(tb_employees),

            tb_employees[Date1] < SelectedDate &&

            (ISBLANK(tb_employees[Date2]) || tb_employees[Date2] > SelectedDate) // here I need to use USERELATIONSHIP(Cal[Date], tb_employees[Date2]) // for calculate correctly the Field tb_employees[Date2]

        ),

        REMOVEFILTERS(Cal), USERELATIONSHIP(Cal[Date], tb_employees[Date2])

    )

The main issues is that I have two relationships between the table and Calendar and for that calculations its neccesary to use both.

Tables and Fields:

tb_employees

tb_Cal (Calendar)

Relationship between tables

I created a Measure but isn't working good because I must use the relation inactive.

I attached the .pbix. (click)

Thank you a lot.

Best Regards,

Hernán Montoya

[lbendlin · Accepted solution]

You will need to define what you mean by "before"  and "after". Your calendar table has day level granularity yet your slicer is only going to month level.  

So - Date 1 is less than the MINIMUM selected date and Date 2 is empty of greater than the MAXIMUM selected date?

Also, your table relationships are not needed in this scenario. You can make both inactive.

Id_Before_Date_Selected = 
var mind = minx(Cal,[Date])
var maxd = maxx(Cal,[Date])
var a = filter(tb_employees,[Date1]<mind && COALESCE([Date2],dt"2030-12-31")>maxd)
return COUNTROWS(SUMMARIZE(a,[id]))