Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Filter and not filter!

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter and not filter!


    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. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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[Business Line] = "Tax"
                        && Table1[Client] = EARLIER ( Table1[Client] )
                )
            )
                > 0
        )
    RETURN
        CALCULATE(
            SUM(Table1[Revenue]),
            Clients
        )

  3. #3
    Board Regular
    Join Date
    Apr 2015
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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[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/e42aa...50901cf58.html

  4. #4
    New Member
    Join Date
    Nov 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    2,555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Filter and not filter!

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

    Excel 2012
    ABC
    1ClientBusiness LineRevenue
    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. #6
    Board Regular
    Join Date
    Apr 2015
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter and not filter!

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

  7. #7
    New Member
    Join Date
    Nov 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter and not filter!

    Quote Originally Posted by AlanY View Post
    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"


    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. #8
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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:
    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:
      Code:
      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
    Last edited by Ozeroth; Jun 13th, 2017 at 10:48 PM.

  9. #9
    New Member
    Join Date
    Nov 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    New Member
    Join Date
    Nov 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks all for your help. I do appreciate your thoughts and advice!
    Last edited by Froggey; Jun 19th, 2017 at 06:37 PM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com