aggregate by Period and using filter to another column

[wildmight2017]

Good afternoon,

Summary: Trying to understand how to create a summarized query - aggregated at selected date slicer period level where Amount_Sum > 0 && Orders_Sum = 0 && Employee_Title = "Worker" and ignore it on the daily leve.  I'm able to get what I need by selecting EMPLOYEE_WID twice (and setting 2nd to DISTINCTCOUNT which is what i need) and then applying visual filter with my condition - but I'd like to be able to create a calculated measure. Basically, if there're multiple cases of this instance during selected time period, I'd just like for it to be 1

I have the following setup:

A) I have daily transaction grain fact table.  DATE_WID, EMPLOYEE_WID, Amount_Sum, Orders_Sum

B)  I have standard date dimension DATE_WID, DATE

C) employee dimension. EMPLOYEE_WID, EMPLOYEE_TITLE

here's what I tried the following formula and it didn't work for me:

CALCULATE(DISTINCTCOUNT('Fact'[EMPLOYEE_WID]), FILTER('Fact','Fact'[Orders_Sum] = 0 && 'Fact'[Amount_Sum] >0), FILTER ('Date', 'Date'[Date] >= MIN('Date'[Date])&&'Date'[Date]<=MAX('Date'[Date])), FILTER('Employee', Employee[Title]="Woker" ))

Basically, I'm trying to ignore daily grain of the exception, and only count weekly ones - (if it happens more than 1 per selected period / week, then it should be counted). 

Thank you - would appreciate any pointers.

[Anonymous]

I'm not exactly sure what you're trying to do but it sound like you want to summarize your table and use a measure on top of this? Maybe you can use SUMX (or COUNTX) measure in combination with the summarized table? Use = SUMMARIZE() and =SUMX().

[wildmight2017]

Thank you for responses....Here's more detailed basic example

employee_id, date, measure 1, measure 2

1, 6-16, 0, 5

1, 6-17, 0, 6

1, 6-18, 0, 5

2, 6-16, 0, 5

2, 6-17, 0, 5

3. 6-15, 1, 5 (does not fit flag criteria)

I would like to get:

employee_id, flag value (measure 1 = 0 && measure 2 > 0 ) 

1, 1

2, 1

3, 0

or if I just select value - 2 (count of flags) 

Currently, I'm getting flag value of 5 because it's counting each day - I'd like to take result set and ignore days. Something like ALLEXCEPT or similar. I'm still trying to figure out Calculate and Filter statements.

Thank you

[Anonymous]

Sound like you want two filters on a distinct count measure. Then you can count unique employee ID's when measure 1 = 0 AND measure 2 > 0. You can use filters in combination with a measure by using the CALCULATE function:  

MyMeasure = CALCULATE( DISTINCTCOUNT(Table1[employee_id]); Table1[ measure 1] = 0; Table1[ measure 2] > 0)

[wildmight2017]

Thank you Lars - this is already happening, but I also need to switch granularity from day to week, i.e. only want to count 1st occurence per week - ignoring other ones. 

[Anonymous · Accepted solution]

Maybe first create this summarized table and on basis of this table create the measure? I'm not sure this is the most efficient way to solve the problem though. 

FILTER(
  FILTER(
    SUMMARIZE( 
      Table1; Table1[employee_id]; Table1[Week]; Table1[measure1]; 
       Table1[measure2]; "mycount"; DISTINCTCOUNT(Table1[employee_id])
    ); 
   Table1[measure1] = 0
 ); 
Table1[measure2] > 0
)

[v-chuncz-msft]

@wildmight2017,

It would be better if you could show us a complete example. It really helps understand your situation.