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?

[Anonymous · Accepted solution]

Hi @Gruja 

ComparisonTable is a calculated table. A calculated table cannot be filtered by filters or slicers in the report. You can consider its data is static when you interact with the report. 

To have a dynamic result displayed, you need to use a measure to populate the column visual. 

A common practice is to add an additional table which has category values to be displayed on x-axis. For example, table 'DimCategory':

Create measures to calculate the Forecasted value and Actual value separately. For example, 

Forecasted = 
SWITCH(
    SELECTEDVALUE('DimCategory'[Category]),
    "Office Space", [Bürobedarf],
    "Storage Space", [Lagerbedarf], 
    "Laboratory Space", [Gesamt Laborbedarf]
)

Actual = 
SWITCH(
    SELECTEDVALUE('DimCategory'[Category]),
    "Office Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1] = "NUF2"), 'Export RDB'[Raum - Fläche in m²]),
    "Storage Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]  = "NUF4"),'Export RDB'[Raum - Fläche in m²]), 
    "Laboratory Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]= "NUF3"), 'Export RDB'[Raum - Fläche in m²])
)

Place the new 'DimCategory' table's [Category] column to X-axis, and place the two new measures to Y-axis. 

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[MFelix]

Hi @Gruja ,

You cannot use measures in Tables, measure are calculated at the time of the visualzaion call and are based on context so they are calculated at need.

When you add a measure to a physical table the context is not consider properly sinvce you change your filter context so they are not properly calculated.

Please check this link with explanation about measures and columns

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/