Forum Discussion

wildmight2017's avatar
wildmight2017
Icon for Advocate II rankAdvocate II
9 years ago
Solved

aggregate by Period and using filter to another column

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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
)

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      wildmight2017
      Icon for Advocate II rankAdvocate II
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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)