topic Re: How To Create A Measure That is Group And Ignores Filters in DAX Commands and Tips

How To Create A Measure That is Group And Ignores Filters

Blue246 — Wed, 13 Mar 2024 21:05:40 GMT

Hi!

So basically i need to create a measure that is grouped and does not get affected a row filter.

Lets say i have a table:

Player	Average Moves Per Day	Team
Ted	7	Red
Bill	2	Blue
Mark	23	Green
Luke	3	Red

Average Moves Per Day is a measure counting rows by person by date.
I need to create a similar measure for Team that shows the average moves per day for the whole team and is not filtered down by Player see below, the output should look like:

Player	Average Moves Per Day	Team	Avg Moves By Team Per Day
Ted	7	Red	5
Bill	2	Blue	2
Mark	23	Green	23
Luke	3	Red	5

I want to see the Average Moves By Team in each row for someone on that team. Since the Red team has 10 total moves divided by two people in the team, each row with a "Red" team member should have the value 5 for the measure etc.

Re: How To Create A Measure That is Group And Ignores Filters

lbendlin — Thu, 14 Mar 2024 00:39:04 GMT

in your measure use REMOVEFILTERS(<player>)

Re: How To Create A Measure That is Group And Ignores Filters

Blue246 — Thu, 14 Mar 2024 13:40:12 GMT

Unfortunately this does not work as when i need to filter by Team, it then shows all Players and not only the players related to the Team filtered.

Re: How To Create A Measure That is Group And Ignores Filters

lbendlin — Thu, 14 Mar 2024 14:12:34 GMT

It will only include the players on the same team as the player in your current context.

Re: How To Create A Measure That is Group And Ignores Filters

Blue246 — Thu, 14 Mar 2024 16:26:52 GMT

This did not work, it just gives an un-rounded version of the average moves column.

Re: How To Create A Measure That is Group And Ignores Filters

lbendlin — Thu, 14 Mar 2024 16:43:24 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How To Create A Measure That is Group And Ignores Filters

Blue246 — Thu, 14 Mar 2024 17:31:56 GMT

Player	Moves	Team	Date
Tim	Left	Red	1/1/2001
Tim	Right	Red	1/1/2001
Tim	Left	Red	1/1/2001
Tim	Down	Red	1/1/2001
Mark	Up	Blue	1/1/2001
Mark	Right	Blue	1/1/2001
Mark	Up	Blue	1/1/2001
Mark	Up	Blue	1/1/2001
Joe	Left	Blue	1/1/2001
Joe	Left	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Bob	Down	Red	1/1/2001
Bob	Up	Red	1/1/2001
Bob	Up	Red	1/1/2001
Bob	Right	Red	1/1/2001

The "Avg Moves Per Day" column comes from a measure:

CALCULATE(

AVERAGEX(VALUES(Date, Moves),

FILTER(Moves>0))

The Moves measure used in the above measure is a count of moves. So the ends result gives me the average moves by player per day when putting player and that measure in a table.

I am trying to do something similar for Team where it gives me Average Moves Per Team like so:

Player	Avg Moves/Day	Team	Avg Team Moves/Day
Tim	4	Red	4
Mark	4	Blue	5
Joe	6	Blue	5
Bob	4	Red	4

Re: How To Create A Measure That is Group And Ignores Filters

lbendlin — Sun, 17 Mar 2024 23:26:47 GMT

Your sample data only includes a single day,

Re: How To Create A Measure That is Group And Ignores Filters

Blue246 — Mon, 25 Mar 2024 21:39:47 GMT

I am having issues making this work because "Moves" is a measure and it won't allow me to pull it into SUMMARIZECOLUMNS. That measure already gets the average moves per day as a whole, i do not need to filter by individual date