Forum Discussion

naa217's avatar
naa217
Regular Visitor
2 years ago
Solved

Linking an aggregated table to main data table

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.

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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's avatar
    dharmendars007
    2 years ago

    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's avatar
    sjoerdvn
    2 years ago

    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.

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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.