SUMMARIZECOLUMNS WITH FILTER

[ajay_gajree]

Hi All

How can I amend the following DAX Table Code to filter the Sentiment Column for the value Positive only? I am able to retrieve it as a column from the 01_Data_v0 1Dashboard table, but I can't work out how to only bring in rows with the value Positive?

BenefitAgg Sentiment =

SUMMARIZECOLUMNS (

'01_Data_v0 1Dashboard'[Brand],

'01_Data_v0 1Dashboard'[Quarter],

'01_Data_v0 1Dashboard'[Market],

'01_Data_v0 1Dashboard'[Sentiment],

"Count", COUNTROWS ( '01_Data_v0 1Dashboard' ),

"Functional", SUM ( '01_Data_v0 1Dashboard'[Good Design] )

+ SUM ( '01_Data_v0 1Dashboard'[Battery Longevity] )

+ SUM ( '01_Data_v0 1Dashboard'[Ease of Use] )

+ SUM ( '01_Data_v0 1Dashboard'[Easily Available] )

+ SUM ( '01_Data_v0 1Dashboard'[No Smell] )

+ SUM ( '01_Data_v0 1Dashboard'[Quality] )

+ SUM ( '01_Data_v0 1Dashboard'[Less Harm] )

+ SUM ( '01_Data_v0 1Dashboard'[Value for Money] )

+ SUM ( '01_Data_v0 1Dashboard'[Nicotine Satisfaction] )

+ SUM ( '01_Data_v0 1Dashboard'[Flavour] ),

"Emotional", SUM ( '01_Data_v0 1Dashboard'[Healthier Lifestyle] )

+ SUM ( '01_Data_v0 1Dashboard'[Feeling Cool] )

+ SUM ( '01_Data_v0 1Dashboard'[Shared Moments] )

+ SUM ( '01_Data_v0 1Dashboard'[Relaxation] )

+ SUM ( '01_Data_v0 1Dashboard'[Modern/Innovative] )

+ SUM ( '01_Data_v0 1Dashboard'[Cool/Stylish] )

+ SUM ( '01_Data_v0 1Dashboard'[Customisation] ),

"CurrentSeason", MAX ( '01_Data_v0 1Dashboard'[IsCurrentQuarter] ),

"PreviousSeason", MAX ( '01_Data_v0 1Dashboard'[IsPreviousQuarter] ),

"QuarterSort", MAX ( '01_Data_v0 1Dashboard'[Quartersort] )

)

[Anonymous]

The measures under SUMMARIZECOLUMNS would be sooooooo much simpler if you unpivoted the table... This is actually what should be done in the first place.

Please remember that fact tables should be long but narrow, dimensions should be short but possibly wide.

Best
D

[Greg_Deckler]

I think you need to use the filterTable parameter:

https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

BenefitAgg Sentiment =

SUMMARIZECOLUMNS (

'01_Data_v0 1Dashboard'[Brand],

'01_Data_v0 1Dashboard'[Quarter],

'01_Data_v0 1Dashboard'[Market],

'01_Data_v0 1Dashboard'[Sentiment],

FILTER('01_Data_v0 1Dashboard',< your filter goes here>),

"Count", COUNTROWS ( '01_Data_v0 1Dashboard' ),

"Functional", SUM ( '01_Data_v0 1Dashboard'[Good Design] )

+ SUM ( '01_Data_v0 1Dashboard'[Battery Longevity] )

+ SUM ( '01_Data_v0 1Dashboard'[Ease of Use] )

+ SUM ( '01_Data_v0 1Dashboard'[Easily Available] )

+ SUM ( '01_Data_v0 1Dashboard'[No Smell] )

+ SUM ( '01_Data_v0 1Dashboard'[Quality] )

+ SUM ( '01_Data_v0 1Dashboard'[Less Harm] )

+ SUM ( '01_Data_v0 1Dashboard'[Value for Money] )

+ SUM ( '01_Data_v0 1Dashboard'[Nicotine Satisfaction] )

+ SUM ( '01_Data_v0 1Dashboard'[Flavour] ),

"Emotional", SUM ( '01_Data_v0 1Dashboard'[Healthier Lifestyle] )

+ SUM ( '01_Data_v0 1Dashboard'[Feeling Cool] )

+ SUM ( '01_Data_v0 1Dashboard'[Shared Moments] )

+ SUM ( '01_Data_v0 1Dashboard'[Relaxation] )

+ SUM ( '01_Data_v0 1Dashboard'[Modern/Innovative] )

+ SUM ( '01_Data_v0 1Dashboard'[Cool/Stylish] )

+ SUM ( '01_Data_v0 1Dashboard'[Customisation] ),

"CurrentSeason", MAX ( '01_Data_v0 1Dashboard'[IsCurrentQuarter] ),

"PreviousSeason", MAX ( '01_Data_v0 1Dashboard'[IsPreviousQuarter] ),

"QuarterSort", MAX ( '01_Data_v0 1Dashboard'[Quartersort] )

)