cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmcmonag Frequent Visitor
Frequent Visitor

Remove Filter Context to give aggregated count in table

Hi, 

 

I'm struggling with a really simple concept, and trying to get a measure which ignores initial filter context coming from a row in a table to correctly aggregate values. Screeshot below shoes the scenario. Basically want to go from table on the LHS to the table on the RHS. I understand why the Count becomes 1 for each MeetingID in the RHS table but am hitting a road block in getting the right DAX to give the 'Count Desired'. Any help much appreciated!

 

daxproblem.pngdaxproblem_model.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Remove Filter Context to give aggregated count in table

try:

Count Desired = 
IF(
    COUNTROWS(FactMeetings) >0,
        CALCULATE(
            COUNTROWS(FactMeetings),
                 FILTER(
                     ALL(FactMeetings),
                     MAX( DimSalesePerson[Initials]) = FactMeetings[Initials]
                  )                      
        )
)

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Remove Filter Context to give aggregated count in table

try:

Count Desired = 
IF(
    COUNTROWS(FactMeetings) >0,
        CALCULATE(
            COUNTROWS(FactMeetings),
                 FILTER(
                     ALL(FactMeetings),
                     MAX( DimSalesePerson[Initials]) = FactMeetings[Initials]
                  )                      
        )
)

View solution in original post

Highlighted
pmcmonag Frequent Visitor
Frequent Visitor

Re: Remove Filter Context to give aggregated count in table

Thanks @Nick_M , that works great! It was the MAX() part I was missing to try and redefine the required context. Though I can see the effect of removing the IF > 0 wrap around, I guess I don't fully understand the theory as why without it the filter between the tables seems to be multiplying out the columns.

Super User
Super User

Re: Remove Filter Context to give aggregated count in table

it has to do with :

FILTER(
                     ALL(FactMeetings),

The way ALL is being used here is to remove filters. There's more too it, but there are many sources out there that have explained this. But just play around with ALL using a table and ALL using a Table Column, ALLEXCEPT. Just see what happens and it will begin to click. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 216 members 2,314 guests
Please welcome our newest community members: