cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Employee

## Add filter to show specific values in calculated columns

Hi all,

Trying to solve following task.  I have data in more or less same format, where final goal is to calculate Aveage value based on Month and Year values:

 Name of colour Certain Value Month Year Average Value Red 1 Oct 2022 Calculation which shows avearge value based month and year Red 4 Nov 2022 Green 5 Sept 2022 Green 2 Aug 2022 Blue 3 Oct 2022 Blue 4 Oct 2022

This caluclation now is done via following DAX column:

AvgValue =
VAR _table =
FILTER (TableName, TableName[Year] = EARLIER (TableName[Year]) && TableName[Month] = EARLIER (TableName[Month]) && TableName[Certain Value] <> BLANK())
RETURN
SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table )

How can I add additional filter to this expression to filter also per name of colour, i.e Red, Green, Blue, so it would be average value per colour value per month+year?

I tried to create 3 additional columns per each colour and add filter values like && TableName[Name of Colour] = "Red" to merge them later into cominbed column , but I still get all values populated in regardless of text value in filter and they are wrong anyways.

What would be  correct way to implement such filtering? Appreciate all advice and help!

1 ACCEPTED SOLUTION
Community Support

Hi @vbvbvb

Modify the formula to:

``````AvgValue Blue =
VAR _table =
FILTER (
TableName,
TableName[Year] = EARLIER ( TableName[Year] )
&& TableName[Month] = EARLIER ( TableName[Month] )
&& TableName[Certain Value] <> BLANK ()
&& 'TableName'[Name of colour] IN { "Blue" }
)
RETURN
IF (
[Name of colour] IN { "Blue" },
SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
)
``````

I add an if condition in the code, tweak the color in different color columns.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

7 REPLIES 7
Employee

That works, thank you very much for all advice!

Community Support

Hi @vbvbvb ,

According to your description, here's my solution. Modify the formula like this:

``````AvgValue =
VAR _table =
FILTER (
TableName,
TableName[Year] = EARLIER ( TableName[Year] )
&& TableName[Month] = EARLIER ( TableName[Month] )
&& TableName[Certain Value] <> BLANK ()
&& 'TableName'[Name of colour] = EARLIER ( TableName[Name of colour] )
)
RETURN
SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
``````

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

Employee

Thanks @v-yanjiang-msft , I will try that approach. Just extra quesiton, how  this condition can be expanded to multiple values, for example Red + Blue + Green (and many more)? Does this method scale up?

`            && 'TableName'[Name of colour] = EARLIER ( TableName[Name of colour] )`

Employee

No, this doesn't work - I need something like this :

Your calculation doesn't filter text values of colour column - I basically need average calculation to happen only if there's hard match of 1 or many values (up to 15 values).

Community Support

Hi @vbvbvb ,

If you want to custom the color, tweak the formula like this:

``````AvgValue Green+Blue =
VAR _table =
FILTER (
TableName,
TableName[Year] = EARLIER ( TableName[Year] )
&& TableName[Month] = EARLIER ( TableName[Month] )
&& TableName[Certain Value] <> BLANK ()
&& 'TableName'[Name of colour] IN { "Green", "Blue" }
)
RETURN
SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
``````

You can see, in the formula I only want to calculate the average of Green and Blue, get the correct result:

You can tweak the formula according to your condition.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

Employee

When I add  this part, column still returns all values for all rows regardless of what actual value 'TableName'[Name of colour] has:

``            && 'TableName'[Name of colour] IN { "Green", "Blue" }``

It doesn't exclude , for example "Red"  from "AvgValue Green+Blue" like in your example, despite DAX and inputs being identical.

It doen't work even in your PBIX example, if change value to only "Blue", it shows "Blue" and "Green":

And if change to "Red" and "Blue", it shows all values:

It seems that filter becames invalid when "month" and "year" are matching :

If there any way to make more explicit filter which filters colour value first and then from it's results looks at month and year for average calculation?

Community Support

Hi @vbvbvb

Modify the formula to:

``````AvgValue Blue =
VAR _table =
FILTER (
TableName,
TableName[Year] = EARLIER ( TableName[Year] )
&& TableName[Month] = EARLIER ( TableName[Month] )
&& TableName[Certain Value] <> BLANK ()
&& 'TableName'[Name of colour] IN { "Blue" }
)
RETURN
IF (
[Name of colour] IN { "Blue" },
SUMX ( _table, TableName[Certain Value] / COUNTROWS ( _table ) )
)
``````

I add an if condition in the code, tweak the color in different color columns.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors