Forum Discussion
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:
This is my calculation for the "text fixed loan count":
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:
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))
6 Replies
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))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...Share the download link of the PBI file.