ALLEXCEPT Not Working - should i use something else?

[arrrlette]

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:

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:

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:

[Ashish_Mathur · Accepted solution]

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))

[arrrlette]

i created this calc:

test3 =

CALCULATE

(

    SUM(Sheet1[Loan Count]),

    ALLEXCEPT('Calendar Table','Calendar Table'[CalendarDate])

)

the calendar table looks like this:

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...

[Ashish_Mathur]

Share the download link of the PBI file.

[arrrlette]

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!!

[Ashish_Mathur]

You are welcome.

[arrrlette]

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.

😞 😞 😞 😞