Need measure to ignore all but one filter

[szub]

Hello,

I need to do a count of serial numbers and I want the count to only be affected by the date range entered in slicer, which is the sale date.  All other filters I want it to ignore (and there a quite a few).  I need to be able to use the measure to then create another measure to divide the total number of serial numbers that had a service claim by the number of serial numbers sold in the sale date range.  I am finding a lot on how to ignore a specific filter, but not on how to only have one filter affect the measure.

Hope this makes sense.

Thank you!

[Tahreem24]

@szub ,

In order to ignore all filter except for one so for this you can utilize the ALLEXCEPT dax function.

Don't forget to give thumbs up 👍and accept this as a solution if it helped you.

[szub]

@Tahreem24 ,

Thank you for your quick reply.  I tried using ALLEXCEPT, but it is still counting values based on filters other than the sale date.   This is the formula I used: 

Serial Count = CALCULATE(COUNT('Sales'[SALES SERIAL_NBR]),ALLEXCEPT('Sales','Sales'[SELLDATE]))

Thank you

[amitchandak · Accepted solution]

When you have everything one table things sometimes becoming challenging.

Create a calendar table and join sales date with and then try allexcept

Serial Count = CALCULATE(COUNT('Sales'[SALES SERIAL_NBR]),ALLEXCEPT('Date','Date'[DATE]))

Or you can use interaction to remove other filters to pass

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[szub]

@amitchandak 

Thank you for the suggestion, but it is still not giving me the incorrect count.  I did use the edit interactions for a card that is displaying the correct number, I just can't seem to get the same count to use in a measurement.

[danextian]

Hi @szub,

 ALLEXCEPT ignores all other columns in a table except for the one/s explicitly specified in the formula. So in your case, the measure will count all 'Sales'[SALES SERIAL_NBR] for each unique 'Sales'[SELLDATE]. Even if you filter it by another filed from Sales table, it will still return the count for a particular sell date or dates.  

To make this clearer, please post a sample data (not an image, either a link to an excel file or just paste a sample table) and your expected output.  Also, are you filtering the report by another column from a related table? If so, this must be taken into account. 

[szub]

@danextian 

Here is some sample data for the sales table.  Yes, I have a relationship to another table that has filters in my model (see 2nd table).

BRAND	Invoice Date	Sales #	Model #	ITEM_DESC	Qty	SERIAL_NBR
WIDGIT	5/20/2019	S123	Model 1	Unit XYZ	1	Serial 1
WIDGIT	11/29/2017	S124	Model 2	Unit XYZ	1	Serial 2
WIDGIT	11/29/2017	S125	Model 3	Unit XYZ	1	Serial 3
WIDGIT	9/20/2019	S126	Model 4	Unit XYZ	1	Serial 4
WIDGIT	9/20/2019	S127	Model 5	Unit XYZ	1	Serial 5
WIDGIT	10/15/2019	S128	Model 6	Unit XYZ	1	Serial 6

Here is a sample of claim data that has a many to one relationship to the serial number in the sales table.

Claim #

Claim Date

Claim Type

Status

Serial #

Svc Agent

Customer

Fail Date

Repair Date

# Trips

Labor

Parts

Other

Total

Claim 1

10/21/2019

Warranty

Paid

Serial 1

SA1

Cust1

10/15/2019

10/15/2019

1

237.2

0

0

237.16

Claim 2

12/22/2018

Installation

Paid

Serial 2

SA2

Cust2

12/13/2018

12/13/2018

1

216.6

0

20.49

237.12

Claim 3

5/17/2019

Warranty

Paid

Serial 2

SA2

Cust2

5/14/2019

5/14/2019

1

250

0

0

250

Claim 4

8/22/2019

Warranty

Paid

Serial 2

SA2

Cust2

8/16/2019

8/16/2019

1

286.2

47.56

4.76

338.48

Claim 5

9/1/2019

Installation

Paid

Serial 3

SA5

Cust3

8/16/2019

8/16/2019

1

629.2

510

51

1190.12

Claim 6

10/1/2019

Warranty

Paid

Serial 3

SA5

Cust3

10/1/2019

10/1/2019

1

100

200

50

350

Thank you

[Jnas]

The problem might be related to specifying the correct column filter to keep. In my case, I initially tried to keep the filter for the column in the table where I was performing the calculation. However, this approach did not work, as the column filter was merely passed down from a table higher up in the relationship hierarchy.

It only worked when I specified the column from a table higher up in the relationship hierarchy. This higher-level table was the one actually being filtered, and it just passed the filter down via the relationship between both tables.

[Tahreem24]

@szub ,

Something like below. In this case I want to keep Date filter but ignore all filters.

Measure = CALCULATE(COUNT('Table'[Values]),ALLEXCEPT('Table','Table'[Date]))

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.