Forum Discussion

ylebec's avatar
ylebec
Regular Visitor
7 years ago
Solved

Table relationship with aggregated data and time slider visual

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

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    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 )

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      ylebec
      Regular Visitor
      7 years ago

      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] ) )
              )