Linking an aggregated table to main data table

[naa217]

I have created an aggregated table in Power BI to summarize how clients have responded to a measure. Specifically, I want to know how many clients have rated items in each category as "None", "Mild", "Moderate", or "Severe". To achieve this, I created measures in my main data source that count the number of times each rating has been selected for each category. I then created a new table to summarize these measures. The DAX code for this summary table is as follows:

CVSummary =

UNION(

    SELECTCOLUMNS(

        { ("Problem Descriptor") },

        "Category", "Problem Descriptor",

        "None", CALCULATE([problemdescriptor_none]),

        "Mild", CALCULATE([problemdescriptor_mild]),

        "Moderate", CALCULATE([problemdescriptor_moderate]),

        "Severe", CALCULATE([problemdescriptor_severe])

    ),

    SELECTCOLUMNS(

        { ("Educational Difficulties") },

        "Category", "Educational Difficulties",

        "None", CALCULATE([Educational_difficulties_none]),

        "Mild", CALCULATE([Educational_difficulties_mild]),

        "Moderate", CALCULATE([Educational_difficulties_moderate]),

        "Severe", CALCULATE([Educational_difficulties_severe])

    ),

    SELECTCOLUMNS(

        { ("Contextual Factors") },

        "Category", "Contextual Factors",

        "None", CALCULATE([contextualfactors_none]),

        "Mild", CALCULATE([contextualfactors_mild]),

        "Moderate", CALCULATE([contextualfactors_moderate]),

        "Severe", CALCULATE([contextualfactors_severe])

    )

)

I then used this aggregated table to create a stacked bar chart that visualizes the distribution of responses across the categories. However, the problem I am facing is that this visual does not interact with other visuals or filters in my report. For example, if I apply a filter to show data for males only, the values in this visual do not update—they remain static.

My Question: Does anyone know how I can ensure that this visual interacts dynamically with other visuals and filters on the report page? I need the values to update based on the filters applied, such as when filtering for specific demographics like gender.

[Sahir_Maharaj · Accepted solution]

Hello @naa217,

One solution is to rewrite the CVSummary table using the SUMMARIZE function:

CVSummary =
SUMMARIZE(
    MainDataTable,  
    MainDataTable[Category], 
    "None", CALCULATE([problemdescriptor_none]),
    "Mild", CALCULATE([problemdescriptor_mild]),
    "Moderate", CALCULATE([problemdescriptor_moderate]),
    "Severe", CALCULATE([problemdescriptor_severe])
)

Hope this helps!

[dharmendars007 · Accepted solution]

Hello @naa217 , 

Thank you for your Question..

Any physical table that is created will not be having any relationship with your main table therefore you cannot apply any filter that can sync to main table..

I would suggest you to create Calculated columns inside the main table with the "If" or "Switch" Condition..I have given a example below which might help you..

Measure  = If([Category] = "Contextual Factors", "None" )
Switch Function Power BI 

Hope this helps..if you find this soultion/suggest helps please mark it as solution and give a Like 👍..

Thanks

Dharmendar S 
LinkedIN Profile 

[sjoerdvn · Accepted solution]

Creating aggregated calculated tables in Power BI hardly ever makes sense. As any calculated table is loaded at the model refresh, the content is not affected by any slicer or filter.
Just let the measures do the aggregations: it is what Power BI was created for to do.
If you do expierence performance issues, you can consider using automatic aggregations.

[dharmendars007 · Accepted solution]

Hello @naa217 , 

Thank you for your Question..

Any physical table that is created will not be having any relationship with your main table therefore you cannot apply any filter that can sync to main table..

I would suggest you to create Calculated columns inside the main table with the "If" or "Switch" Condition..I have given a example below which might help you..

Measure  = If([Category] = "Contextual Factors", "None" )
Switch Function Power BI 

Hope this helps..if you find this soultion/suggest helps please mark it as solution and give a Like 👍..

Thanks

Dharmendar S 
LinkedIN Profile 

[Sahir_Maharaj · Accepted solution]

Hello @naa217,

One solution is to rewrite the CVSummary table using the SUMMARIZE function:

CVSummary =
SUMMARIZE(
    MainDataTable,  
    MainDataTable[Category], 
    "None", CALCULATE([problemdescriptor_none]),
    "Mild", CALCULATE([problemdescriptor_mild]),
    "Moderate", CALCULATE([problemdescriptor_moderate]),
    "Severe", CALCULATE([problemdescriptor_severe])
)

Hope this helps!