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