Search code examples
powerbiaggregatedaxfilteringpowerbi-desktop

Aggregating a dimension table by fact table in PowerBI

I have a hierarchy of three PowerBI tables

Courses:

Course ID Course credits
C1 2
C2 3

Practical Courses:

Practical Course ID Course ID Year Number of Students
C1.22 C1 2022 10
C1.23 C1 2023 15
C2.21 C2 2021 17

Practical course lecturers:

Lecturer name Practical Course ID
Jack C1.22
Jack C1.23
Jill C1.22
Jill C2.21

Note that one course may have multiple practical courses and that one practical course may have multiple lecturers.

I would like to find the total number of credits and students for each lecturer. For the given sample data, I would like to get the following results

Lecturer name Credits Students
Jack 4 25
Jill 5 27

The problem is that the filters go the "wrong" way. I somehow need to aggregate the metadata filtered by the fact table value.

Is there a simple way of doing this without resorting to bidirectional filters? This is a general problem I encounter in multiple forms.

Edit: I was asked whether the following thread answers my question Avoiding bidirectional filter for a matrix report

There are two answers that are suggested there:

  1. Use a cross-filter to define an ad-hoc filter for this specific query.
  2. Use a common field that appears in the fact table.

I don't see how these answers apply to this question. In particular, it seems cumbersome to set two cross-filters to connect the fact table with the top-level table (There may be multiple levels). The second suggestion isn't relevant since there is no common field between the first and third tables.

Solution

  • @peter is correct. I'll make the following points.

    1. You have a snowflake schema. That is why " it seems cumbersome to set two cross-filters to connect the fact table with the top-level table"

    2. Your sample data doesn't match your results. I have corrected this in my example below.

    enter image description here

    1. Here is how you achieve your desired result.

    enter image description here

    enter image description here

    Measure 1:

    Students = CALCULATE( SUM('Practical Courses'[Number of Students]),  CROSSFILTER('Practical Courses'[Practical Course ID] , 'Practical Course Lecturers'[Practical Course ID], Both))

    Measure 2:

    Credits = SUMX('Practical Course Lecturers', CALCULATE(SUM(Courses[Course credits]),  CROSSFILTER('Practical Courses'[Practical Course ID] , 'Practical Course Lecturers'[Practical Course ID], Both),CROSSFILTER(Courses[Course ID], 'Practical Courses'[Course ID], Both) ))

    There are 3 ways to aggregate a dimension from a fact if you don't use bi-directional filters.

    1. Pass whole fact table as a filter in Calculate (not good)
    2. Use cross filter in calculate
    3. Use TREATAS()