Forum Discussion

michaelccdf's avatar
michaelccdf
Helper I
8 years ago
Solved

RelatedTable

I have three tables   factClosures Date,School,PeriodsClosed   dimSchools School, CountofHomerooms   dimDates Date, Week   I'm trying to find the PeriodsClosed Per Homeroom Per Week.  The ...
Need Help
  • popov's avatar
    popov
    8 years ago

    I read your first post again and understud.

    Try this way:

    1. Edit relationship between factClosures and dimSchools and set Filter Direction = "To both tables" 

    2. Replace Measure formula:
    PeriodsClosed Per Homeroom =
    CALCULATE (
        DIVIDE (
            SUM ( factClosures[PeriodsClosed] );
            SUM ( dimSchools[CountofHomerooms] )
        )
    )

michaelccdf's avatar
michaelccdf
Helper I
8 years ago

thank you popov, this is almost what I am looking for.  The difficulty is that I only need the total homerooms for schools that reported in a given week.  Something like

 

sum(all the periods closed in a given week) / sum(homerooms of distinct schools that reported in a given week)

 

in other words, I need to group by week and school

 

does that make sense?  Thanks a lot for the help. 

popov's avatar
popov
Resolver III
8 years ago

I read your first post again and understud.

Try this way:

1. Edit relationship between factClosures and dimSchools and set Filter Direction = "To both tables" 

2. Replace Measure formula:
PeriodsClosed Per Homeroom =
CALCULATE (
    DIVIDE (
        SUM ( factClosures[PeriodsClosed] );
        SUM ( dimSchools[CountofHomerooms] )
    )
)

  • michaelccdf's avatar
    michaelccdf
    Helper I
    8 years ago

    wow, this works great!  Setting cross filter direction to both unlocks the magic!  Thanks so much.