Link table data with measures data and display in a visual

[Gruja]

I’m working on a Power BI report and need assistance with combining forecasted and actual space data, while ensuring the ability to filter the data by Kostenstelle (department). Below is my data model setup and the approach I’m using:

Data Model:

I have a few key tables in my model:

1. • Kostenstelle (group identifier)
   • Subdepartment
   • Department

     dimNutzerstructure (dimension table containing user categories):

Example entries:

Kostenstelle	Subdepartment	Department
021102	Financial_1	Banking
021103	Financial_2	Banking
011112	Personal	HR

2. ExportRDB (room data):

• RoomID
• Kostenstelle
• Fläche in m² (area)
• DIN277-1 (room type: e.g., NUF2 for office, NUF4 for storage)

Example entries:

RoomID	Kostenstelle	Fläche in m² (area)	DIN277-1 (type)
1	021102	10	NUF2
2	021102	20	NUF4
3	011112	15	NUF2

3. factPersonaltapete (user data):

• Kostenstelle
• Name
• Department

Example entries:

Kostenstelle	Name	Department
021102	John Doe	Banking
011112	Max Mustermann	HR

Measures:

I am using the following measures to calculate space needs for each user category:

1. Bürobedarf (forecasted office space):

Bürobedarf = 
SUMX(
    VALUES('factPersonaltapete'[MA-Kategorie]), 
    [Anzahl Angestellte] * 
    LOOKUPVALUE(
        'Bueroflaechenrichtwerte'[Büro - Richtwert, m²],
        'Bueroflaechenrichtwerte'[Dienstbezeichnung], 
        'factPersonaltapete'[MA-Kategorie]
    )
)

2. Anzahl Angestellte (number of employees):

Anzahl Angestellte = COUNTROWS('factPersonaltapete')

3. Lagerbedarf (forecasted storage space):

Lagerbedarf = [Bürobedarf] * 0.05

To get the actual amount of office and storage space, I calculate the sum of the Fläche in m² column from the ExportRDB table, categorized by the DIN277-1 column (with values NUF2 for office space and NUF4 for storage space).

Approach:

I created a ComparisonTable using UNION to combine forecasted and actual space data:

ComparisonTable = 
UNION(
    ROW("Category", "Office Space", "Forecasted", [Bürobedarf], "Actual", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1] = "NUF2"), 'Export RDB'[Raum - Fläche in m²])),
    ROW("Category", "Storage Space", "Forecasted", [Lagerbedarf], "Actual", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]  = "NUF4"),'Export RDB'[Raum - Fläche in m²])),
    ROW("Category", "Laboratory Space", "Forecasted", [Gesamt Laborbedarf], "Actual", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]= "NUF3"), 'Export RDB'[Raum - Fläche in m²])
))

Result

Issue:

I am unable to filter this visual with the Kostenstelle column to display both the used and forecasted space for each department.

Question:

1. Is the approach I’m using reasonable, or am I missing something in my logic?
2. How can I combine the data from these measures with the data from the ExportRDB table, while still enabling filtering by Kostenstelle (department) in my visual?