SummarizeColumns with Calculatedtable is not working as expected

[GreeshmaN]

Hello,

I have a fact table called fact_A and a dimension table called dim_mas_prd. I am using summarizecolumns with caluculatedtable functions in the cascading parameters for the paginated report to filter the data which worked fine, but all of a sudden its not filtering the data.
The below is the query in Paginated report:

CALCULATETABLE (

    SUMMARIZECOLUMNS (

    'fact_A'[prd_id]),

    'dim_mas_prd'[mas_prd_id] = "value"

)

Value is an other parameter.

The above function used to filter the prd_id based on the selection of master_prd_id but recently it's not filtering the data. Is there anything wrong with the functions used? But they did work before, so am confused.

Any help would be appreciated.

Thank you

[GreeshmaN]

Hi @Bibiano_Geraldo 

I found the issue why CalculateTable and SummarizeColumns are not working. During July 2024, Microsoft updated the functional behaviour of SummarizeColumns function with CalculateTable functions, because of this behavioural change done my Microsoft the cascading parameters are not working.

https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax#background

Thank you
Greeshma 

[GreeshmaN]

All of your solutions work, but my concern is the function that I shared with Summarizecolumns used to filter the data but its not filtering the data.

Would like to know if there is any issue with Summarizecolumns and calculatetable, as it worked before.

[Bibiano_Geraldo]

Hi @GreeshmaN ,

The issue seems to be related to how SUMMARIZECOLUMNS and CALCULATETABLE interact. While SUMMARIZECOLUMNS creates a summarized table, VALUES returns distinct values, and they may behave differently in certain contexts. The problem might stem from changes in the data model or filters not being applied correctly. If the filter 'dim_mas_prd'[mas_prd_id] = "value" isn’t working, it can prevent SUMMARIZECOLUMNS from filtering the data. To diagnose the issue, try testing the filter alone with CALCULATETABLE without SUMMARIZECOLUMNS to see if it works:

CALCULATETABLE(
    'fact_A',
    'dim_mas_prd'[mas_prd_id] = "value"
)

If this works, the issue might lie in the way SUMMARIZECOLUMNS is interacting with the filter. Check if there are any changes in your data model or relationships that could affect the filter.

[GreeshmaN]

For the below one:

CALCULATETABLE(
    'fact_A',
    'dim_mas_prd'[mas_prd_id] = "value"
)

I am getting an error message: Query (9, 5) A single value for column 'prd_id' in table 'fact_A' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Bibiano_Geraldo]

Hi @GreeshmaN ,
Try Using FILTER Instead of Direct Condition in CALCULATETABLE:

CALCULATETABLE (
    SUMMARIZECOLUMNS ( 'fact_A'[prd_id] ),
    FILTER ( ALL ( 'dim_mas_prd' ), 'dim_mas_prd'[mas_prd_id] = "value" )
)

[GreeshmaN]

The above logic doesn't filter the data. There is some issue between Calculatetable & Summarizecolumns when both used together.

[Bibiano_Geraldo]

Hi @GreeshmaN ,

To achieve your goal, please try this DAX:

CALCULATETABLE (
    VALUES('fact_A'[prd_id]),
    'dim_mas_prd'[mas_prd_id] = "value"
)

[Anonymous]

Hi @GreeshmaN 

Please try the following two versions of the DAX formula and see if they help you.

CALCULATETABLE (
SUMMARIZECOLUMNS (
'fact_A'[prd_id],
FILTER (
'dim_mas_prd',
'dim_mas_prd'[mas_prd_id] = "value"
)
)
)

CALCULATETABLE (
SUMMARIZE (
'fact_A',
'fact_A'[prd_id]
),
'dim_mas_prd'[mas_prd_id] = "value"
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.