cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tan Advocate I
Advocate I

Re: Make measure ignore specific filter

Would you please let me know how did you resolve this I am facing similar issue. TIA

AkhilAshok Solution Sage
Solution Sage

Re: Make measure ignore specific filter

This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

 

 

View solution in original post

Lasselakan Advocate III
Advocate III

Re: Make measure ignore specific filter

Thank you! Finally someone cares to understand the problem thoroughly and not just throws out a guess!
KMRP_1234
Regular Visitor

Re: Make measure ignore specific filter

I have created a different distinct values table for Month+Year, and used ALL( Table2[Month+Year]) in CALCULATE function. Still doesn't work for me! any other solution I can try out? 

newtrainee
Frequent Visitor

Re: Make measure ignore specific filter

@Greg_Deckler 

Hi Greg,

 

My measure is giving wrong column sub total but correct row sub total.

 

My Actual Measure in the report:

 

Measure =

VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name])]))

VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

Return x/Y

Output I am getting:
 
EmployeeProject1Project2Total
A0.300.3
B0.210.190.25(NOT CORRECT)
C00.30.3
Total0.510.491


It is coming wrong as the measure is behaving at the backend like below ie ( Filtering across the project)
 
Measure = 
VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name]),ALLSELECTED('Project Details'[Complete Project Detail]))  <--- Filtering

VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

Return x/Y

Can you please suggest the solution as I am unable to avoid filtering and fix this
charleshale Advocate II
Advocate II

Re: Make measure ignore specific filter

Can you try calculate ( [measure], allexcept ( .......the one item you're trying to have work as a filter....)?

 

And if you need to, you could go to the table that your filtering against and add custom columns to allow easier filters

EP
New Member

Re: Make measure ignore specific filter

Thank you @kxtayl18!! Solved my problem perfectly!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors