Forum Discussion

adoster's avatar
adoster
Resolver I
2 years ago
Solved

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

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:

UserDateWidgets MadeHours WorkedTestFilter
10018/1/202311Green
10018/1/202311Red
10028/1/202311Green
10028/2/202311Green

 

Results with no Filter
UserTotal Days WorkedTotal Widgets
100122
100222

 

Desired Results (Page Filter on TestFilter = Green)
UserDays WorkedTotal WidgetsAVG Widgets August
1001210.5
1002221

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

 

Thank you for any assistance!

Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    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] )

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    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's avatar
      adoster
      Resolver I
      2 years ago

      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!

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    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's avatar
      adoster
      Resolver I
      2 years ago
      I added DistinctCount but your code basically worked. Thank you!
       
      WorkedDays =
      CALCULATE(
          DISTINCTCOUNT('Table'[Final Date]),
          REMOVEFILTERS('Table'[TestFilter])
      )