DAX - Summarize Filter Logic

[Anonymous]

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:

ID	Check Date	Hours
124	1/10/2020	10
124	1/10/2020	10
124	1/3/2020	15
548	1/3/2020	5
548	1/10/2020	6

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:

124	35
548	11

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:

124	20
548	6

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!

[az38 · Accepted solution]

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]

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?