Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX - Summarize Filter Logic

Dear Power BI Community,

 

I am trying to accomplish a histogram of the distribution of overtime hours by employees. Axis = Total Hours (Groups), Values = Total Hours.

 

My table is set up like this from a report:

IDCheck DateHours
1241/10/202010
1241/10/202010
1241/3/202015
5481/3/20205
5481/10/20206

 

Now what I did was create a summarize table with the following dax:

 

 

Summarize(Table1,Table1[ID],"Total Hours",SUM(Table1[Hours])

 

 

 

I get the following table:

12435
54811

 

This is what I want the table to look like, and I created a relationship between the two tables on the ID column setting cross-filtering to "Both".

 

When I go to only look at a Check Date of 1/10/2020 the answer I should get is:

12420
5486

 

But the summarize table is not being changed at all it is pulling in the total still and not recognizing the "Check Date" filter.

 

My initial thought is it has to do with Sum(Hours) in the Summarize logic.

 

Any help is appreciated!

  • Hi!

    calculated table will not affected by slicers

    but you can create a measure in your SummarizeTable

    TotalHoursMeasure = calculate(SUM('Table1'[Hours]),FILTER(ALLEXCEPT('Table1';Table1[Check Date]),Table1[ID]=SELECTEDVALUE('SummarizeTable'[Table1_ID])))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion

    Hi!

    calculated table will not affected by slicers

    but you can create a measure in your SummarizeTable

    TotalHoursMeasure = calculate(SUM('Table1'[Hours]),FILTER(ALLEXCEPT('Table1';Table1[Check Date]),Table1[ID]=SELECTEDVALUE('SummarizeTable'[Table1_ID])))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thats awesome thank you - I was hoping to be have this as a column so then I can do a histogram off of the total hours, any ideas?