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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
arrrlette
Frequent Visitor

ALLEXCEPT Not Working - should i use something else?

i have a very simple table that i wrote a custom sql statement for. no relationships, just a single table - purpose was to test this.

 

i am trying to do the equivalent of a fixed calc form tableau. I want to take a sum of [Loan Count] regardless of any slicers and filters, EXCEPT for the disburementdate - which is a slicer the user can choose:

arrrlette_0-1709346005627.png


This is my calculation for the "text fixed loan count":

 

test fixed loan count =
CALCULATE(
    SUM(Query1[Loan Count]),  
    ALLEXCEPT(Query1, Query1[DisbursementDate])
)

as you can see in the picture above, i have an additional slicer for "Branch". I would expect that with the ALLEXCEPT expression i used, it would ignore that slicer. But, it does not:

arrrlette_1-1709346114531.png

and it gives me some number i have no idea about...

 

maybe i shouldnt be using ALLEXCEPT - i want it to ignore the filter (the slicer) selections other than disbursement date.


Any advice guys? This has been so frustrating. Thank you!


I tested this with an excel version too and same thing - this is what my excel looks like:

arrrlette_0-1709349045988.png

 





1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table and to your slicer/filter/visual, drag Date from the Calendar Table.  Create a relationship (many to one and single) from the Disbursement date to the Date column of the Calendar Table.  Write this measure

test fixed loan count = CALCULATE(SUM(Query1[LoanCount]),ALLEXCEPT(Calendar))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table and to your slicer/filter/visual, drag Date from the Calendar Table.  Create a relationship (many to one and single) from the Disbursement date to the Date column of the Calendar Table.  Write this measure

test fixed loan count = CALCULATE(SUM(Query1[LoanCount]),ALLEXCEPT(Calendar))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

i created this calc:

test3 =
CALCULATE
(
    SUM(Sheet1[Loan Count]),
    ALLEXCEPT('Calendar Table','Calendar Table'[CalendarDate])
)

the calendar table looks like this:

arrrlette_0-1709357305420.png

 

and i am using a slicer now that uses the calendar date. However the same issue still exists - i.e. when i select a branch on the slicer, it changes the value.

😞


Also, why is a calendar table necessary? I appreciate the help!! Spent hours on this already...

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

IT WORKED!!

That was exactly what i needed to do, thank you so much!!
The error i made was putting the wrong table name in the query...your suggestion was perfect. 

THANKS SO MUCH!!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
arrrlette
Frequent Visitor

so i added .[date] after disbursement date and that got rid of the issue where it wasnt ignoring the branch filter (i.e. now it does).

 

BUT

 

Now its also ignoring the disbursementdate filter! So it pulls the count for the entire dataset time period.

 

😞 😞 😞 😞

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.