Filtering a Disconnected Calendar

[frankee_gee]

Hello Everyone, Hoping for some help with Filtering a Disconnected Calendar


table 1: Report "data table"

working with 3 columns in "Report" table
-Report[IDNo]
-Report[OutDate]
-Report[InDate]


table 2: Calendar "Connected calendar : t_Calendar[Date] to Report[OutDate]

table 3: CalendarFree "Disconnected Calendar"



Created the following meassure: IDNoCount:=DISTINCTCOUNT(Report[IDNo])

Need a count of all IDNoCount w/ OutDate and a count of all InDate


It would like something like :

OutDateIDNoCount:= Calculate(
[IDNoCount]
Filter(Report[OutDate]<Max CalendarFree[Date])


I'm missing something because currently it's returning huge numbers.

Thanks for any and all help.

 

[tallan]

If [IDNo] is unique in every row of Report, you could also use COUNTROWS and take advantage of the current relationship:

OutDateIDNoCount := COUNTROWS ( Report )

and for InDateIDNoCount, create an additional relationship between Report[InDate] and t_Calendar[Date] which the data model will mark as inactive (as it should). Then

InDateIDNoCount := CALCULATE ( COUNTROWS ( Report ), USERELATIONSHIP ( Report[InDate], t_Calendar[Date] ) )

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI