Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
az38
Community Champion
Community 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


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

View solution in original post

2 REPLIES 2
az38
Community Champion
Community 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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors