Thanks:  0
Likes:  0

# Thread: Filter and not filter!

1. ## Filter and not filter!

Hi all,

Not sure if you can help me here and this might take a moment to explains. A sample of my data set is as follow:

 Client Business Line Revenue Bob Jones Audit \$100,000 Paul Simon Tax \$100,000 Paul Simon Audit \$100,000

I'm looking at cross sales. In the example above Paul Simon has two products. I need to create a measure which will give me:

NO FILTER
 Client Audit Tax Bob Jones \$100,000 Paul Simon \$100,000 \$100,000

FILTER ON AUDIT
 Client Audit Tax Bob Jones \$100,000 Paul Simon \$100,000 \$100,000

FILTER ON TAX
 Client Audit Tax Paul Simon \$100,000 \$100,000

I basically need to see the revenue amount for the client irrespective of the filter. If I select Tax it should give me all clients with revenue for Tax and then the Revenue for the other business lines.

Happy to elaborate more if necessary. This is causing me a lot of pain. I've currently created calculated columns but this is causing performance issues and I'll like to see if I can do this with measures instead.

If I use calculate with (Filter = Audit) then it will continue to show the record for Bob Jones when I filter for Tax where in reality I only want the Revenue amount if there is a value for Audit AND a value for Tax.

Any help would be appreciated! Thanks all

2. ## Re: Filter and not filter!

Interesting. You almost want the reverse of ALLSELECTED().

I don't know how to do that, so I thought about a disconnected slicer with separate measures for Audit and Tax. I tried the below (and the equivalent Audit version and it returns the correct totals and value in the tax column, but the audit column is shown blank. Not sure why.

Code:
```TaxSales =
VAR
Clients =
FILTER (
VALUES ( Table1[Client] ),
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
&& Table1[Client] = EARLIER ( Table1[Client] )
)
)
> 0
)
RETURN
CALCULATE(
SUM(Table1[Revenue]),
Clients
)```

3. ## Re: Filter and not filter!

Think I cracked it. Missing ALL (), though I won't pretend to know why that fixed it.

Code:
```AuditSales =
VAR
Clients =
FILTER (
VALUES ( Table1[Client] ),
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
All ( Table1 ),
&& Table1[Client] = EARLIER ( Table1[Client] )
)
)
> 0
)
RETURN
SUMX(
Clients,
CALCULATE ( SUM ( Table1[Revenue] ) )
)```
Then another measure TaxSales with "Audit" replaced with "Tax". Then wrap the whole thing up with a disconnected slicer control.

4. ## Re: Filter and not filter!

Hi, thanks very much for this. I'll give it a try first thing tomorrow morning. I was thinking of another solution too. I'll let you know which solution works.
One thing i might need to bear in mind is that I'm on excel 2013 and that doesn't support var.
I'll be back tomorrow. 😊

5. ## Re: Filter and not filter!

first of all, shouldn't your data sample looks like this?

Excel 2012
ABC
2Bob JonesAudit
3Paul SimonTax\$100,000
4Paul SimonAudit\$100,000

Sheet1

i.e. nothing in C2.

If so, after you select the audit filter you can select the second filter "Revenue" by deselect "blank"

6. ## Re: Filter and not filter!

Right. I would think you can unwind the VAR part and just have a regular formula

7. ## Re: Filter and not filter!

Originally Posted by AlanY
first of all, shouldn't your data sample looks like this?

Excel 2012
A B C
2 Bob Jones Audit
3 Paul Simon Tax \$100,000
4 Paul Simon Audit \$100,000
Sheet1

i.e. nothing in C2.

If so, after you select the audit filter you can select the second filter "Revenue" by deselect "blank"

Hi AlanY no my data example was right. Bob isn't a cross sale so should appear when I select Audit and Paul Simon is so should appear whether I select Audit or Tax.

8. ## Re: Filter and not filter!

Hi Froggey,

What you are trying to do is an example of Basket Analysis.
You can adapt the pattern presented on DAX Patterns here:

In your case, you want to select a Business Line, then show revenue for all clients (in all Business Lines) who have revenue in that Business Line.

I'll call your sample table Revenue.

Following the pattern on DAX Patterns you can:
1. Create a 'Filter Business Line' table containing a column 'Filter Business Line' with distinct Business Lines (Audit & Tax in your example)
3. Create a measure like:
Code:
```Revenue for Clients with Filtered Business Line =
CALCULATE (
SUM ( Revenue[Revenue] ),
CALCULATETABLE (
SUMMARIZE ( Revenue, Revenue[Client] ),
)
)```

Anyway, I suggest reading the DAX Patterns page and trying this out.

Cheers,
Owen

9. ## Re: Filter and not filter!

Thanks Owen, I'll have a look at this later today.

I've decided to re-build the report as I'm having too many performance issues - one change to a calc takes about 2 hours to calculate Starting from scratch will allow me to find out what is causing this.

When I get to the measures stage I will try both yours and gazpage's although I'm not sure I'm good enough yet to convert gazpages into a 2013 formula. lol

I'll let you know where I end up.

10. ## Re: Filter and not filter!

Hey all quick update...

I didn't manage to get the calculation from a custom column to a measure. Maybe I'm not good enough at the minute to figure it out and I struggled a little with the above formulas. Once I've got through this busy period I'll go back and look in more detail and will try to figure it out.

Basically I had to rebuild the report. At first I couldn't even replicate the existing calculated columns. It seems for some reason distinctcount in a calculated column just causes the file to slow down to the point of stopping. I made a measure instead and referenced that in the column. Now the report is working, refreshing on SharePoint and I'm about to launch.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•