Create a Measure which ignores specific Filter not working: Using ALL()

[adoster]

Hello,

I am trying to calculate the count of Days a User worked at least 1 hour while ignoring a specific page filter.

Each Day should only be counted once. Other Filters should still work.

Here is my Measure:

WorkedDays =

CALCULATE(

    COUNTROWS(

        SUMMARIZE(

            FILTER(TABLEA, TABLEA[WorkedHours] > 0),

            TableA[Date]

        )

    ),

    ALL(TABLEA[TESTFILTER]),   // Remove filter effect of TESTFILTER

    ALLSELECTED(TABLEA),

    FILTER(TABLEA, TABLEA[WorkedHours] > 0)

)

When I set a TESTFILTER to Green it drops my total for Worked Days but should not.

I need this value to be static so that I can find the average Widgets Produced for all days worked.

Here is a sample table and expected results:

User	Date	Widgets Made	Hours Worked	TestFilter
1001	8/1/2023	1	1	Green
1001	8/1/2023	1	1	Red
1002	8/1/2023	1	1	Green
1002	8/2/2023	1	1	Green

Results with no Filter
User	Total Days Worked	Total Widgets
1001	2	2
1002	2	2

Desired Results (Page Filter on TestFilter = Green)
User	Days Worked	Total Widgets	AVG Widgets August
1001	2	1	0.5
1002	2	2	1

Currently w/ the page filter set I get 1 Day worked for 1001

Thank you for any assistance!

[parry2k]

@adoster beautiful, good to hear. Cheers!

[parry2k · Accepted solution]

@adoster try these measures:

Days Worked = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table'[TestFilter] ) )

Total Widgets = SUM ( 'Table'[Widgets Made] )

Avg Widgets = DIVIDE ( [Total Widgets], [Days Worked] )

[adoster]

I added DistinctCount but your code basically worked. Thank you!

WorkedDays =

CALCULATE(

    DISTINCTCOUNT('Table'[Final Date]),

    REMOVEFILTERS('Table'[TestFilter])

)

[parry2k]

@adoster it will easier if you can share sample pbix using one drive/google drive and the expected output. Without knowing your data model and required output, it is very hard to decode the DAX expression.

[adoster]

Hello,

I edited the original post and included sample data.

This is a slimmed down version of my original data which I am unable to post.

Thanks!