Forum Discussion

GreeshmaN's avatar
GreeshmaN
Advocate I
1 year ago

SummarizeColumns with Calculatedtable is not working as expected

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
General Comment
Need Help
Show and Tell

8 Replies

  • GreeshmaN's avatar
    GreeshmaN
    Advocate I
    1 year ago

    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's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      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's avatar
        GreeshmaN
        Advocate I
        1 year ago
        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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi GreeshmaN ,

    To achieve your goal, please try this DAX:

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