Table relationship with aggregated data and time slider visual

[ylebec]

Hi,

I have a table with the scheduled services to be operated depending by day type (Weekday, Saturday, Sunday)

Then another tablewith thte actual services operated over the first 5 days of February. Note service number 9 was missed on the 1st, 4th and 5th Feb.

Now I'm elaborating a Summary Table in which I need to show the number of Schedueld Services (fixed number) vs Operated ones depending on the day I select on a Time Slider. I.e, if I select only the 1st Feb, as it's a Weekday, then I'll get Scheduled Services = 6 vs Operated = 5 (since service nr 9 was missing). For the 2nd Feb, Scheduled 2, Operated 2 (all operated), and if I select an interval of 4 days for example from the 2nd to the 5th (1xSat, 1xSun, 2xWeekday) then Scheduled = 16, Operated = 14 (service 9 missing twice on that timeframe).

Any ideas on how achieve this? Thanks

[v-chuncz-msft · Accepted solution]

@ylebec,

Add a calculated column and use two measures.

Type =
SWITCH ( WEEKDAY ( Table2[Date] ), 1, "Sunday", 7, "Saturday", "Weekday" )

Measure =
SUMX (
    VALUES ( Table2[Type] ),
    CALCULATE (
        COUNTROWS (
            FILTER ( Table1, Table1[Day of Operation] = SELECTEDVALUE ( Table2[Type] ) )
        )
            * DISTINCTCOUNT ( Table2[Date] )
    )
)

Measure 2 =
COUNTROWS ( Table2 )

[ylebec]

Thank you for that!

One thing though, if I shorten the formula to the one below I get the same results. What's the DistinctCount function doing there?

COUNTROWS (
            FILTER ( 'Data - Work Report', 'Data - Work Report'[Day Type] = SELECTEDVALUE ( Data[Day Type] ) )
        )