Filter and not filter!

[Froggey]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

FILTER ON AUDIT

Client	Audit	Tax
Bob Jones	$100,000
Paul Simon	$100,000	$100,000

<tbody>
</tbody>

FILTER ON TAX

Client	Audit	Tax
Paul Simon	$100,000	$100,000

<tbody>
</tbody>

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

 

[gazpage]

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.

TaxSales = 
VAR 
    Clients =  
    FILTER (
        VALUES ( Table1[Client] ),
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[Business Line] = "Tax"
                    && Table1[Client] = EARLIER ( Table1[Client] )
            )
        )
            > 0
    )
RETURN
    CALCULATE(
        SUM(Table1[Revenue]),
        Clients
    )

 

[gazpage]

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

AuditSales = 
VAR 
    Clients =  
    FILTER (
        VALUES ( Table1[Client] ),
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                All ( Table1 ),
                Table1[Business Line] = "Audit"
                    && 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.

https://expirebox.com/download/e42aa990d64e2c14ce02dfd50901cf58.html

 

[Froggey]

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

 

[AlanY]

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

Excel 2012
	A	B	C
1	Client	Business Line	Revenue
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"

 

[gazpage]

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

 

[Froggey]

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

Excel 2012

	A	B	C
1	Client	Business Line	Revenue
2	Bob Jones	Audit
3	Paul Simon	Tax	$100,000
4	Paul Simon	Audit	$100,000

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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.

 

[Ozeroth]

Hi Froggey,

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

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)
2. Relate Revenue[Business Line] to 'Filter Business Line'[Filter Business Line] with an inactive relationship
3. Create a measure like:
   

   Revenue for Clients with Filtered Business Line =
   CALCULATE (
       SUM ( Revenue[Revenue] ),
       CALCULATETABLE (
           SUMMARIZE ( Revenue, Revenue[Client] ),
           ALL ( Revenue[Business Line] ),
           USERELATIONSHIP ( Revenue[Business Line], 'Filter Business Line'[Filter Business Line] )
       )
   )

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

Cheers,
Owen

 

[Froggey]

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.

 

[Froggey]

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.

Thanks all for your help. I do appreciate your thoughts and advice!