RelatedTable

[michaelccdf]

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 trouble is, the CountofHomerooms field in dimSchools does not seem to "link" to factClosures in PowerBI.  

In SQL I would do something like the below to calculate this (there is probably a better way to do this, I'm not at all a SQL expert)

SELECT

Week,

SUM(PeriodsClosed) / SUM(CountofHomerooms) as PeriodsClosedPerHomeroom

FROM

(SELECT

dimDates.Week,

dimSchools.School,

dimSchools.CountofHomeRooms,

SUM(factClosures.PeriodsClosed) as PeriodsClosed

FROM factClosures

JOIN dimSchools ON factClosures.School = dimSchools.School

JOIN dimDates ON factClosures.Date = dimDates.Date

GROUP BY 

dimDates.Week,

dimSchools.School,

dimSchools.CountofHomeRooms) as query1

GROUP BY week

Any ideas how I might do this in PowerBI directly ?

[popov]

Hello. 
Follow steps below:

1. Add tables to Power BI Desktop

2. Create relationship between factClosures and dimDates, and between factClosures and dimSchools

3. Create claculated column in table factClosures using formula:

CountofHomerooms=RELATED(dimSchools[CountofHomerooms])

4. Create Measure 
PeriodsClosed Per Homeroom =
CALCULATE (
    DIVIDE (
        SUM ( factClosures[PeriodsClosed] );
        SUM ( factClosures[CountofHomerooms] )
    )
)

[michaelccdf]

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 · Accepted solution]

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]

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