Forum Discussion

jhenscheid1's avatar
jhenscheid1
Frequent Visitor
3 years ago

Using VAR in measure - adding a filter

Hello, I have the below measure in a report which is providing data on "Opened" accounts.  I am tying sales to this so we can understand how well we are doing each Fiscal week.  To provide a little background we began measuring the sales and opened accounts in week 9 (when the project started).  We are finding recently, however, that many accounts in a certain category were opened before week 9.  But for various reasons, the manager does not want to include them until week 47 (the week they were updated).  They are considered "National" accounts and are in a different table (Sales) than the Amount Ordered table (referenced in the measure below).

 

So my question is how to add a filter in the measure to exclude "National" accounts from being considered a new account.

 

Thanks

 

New Acct =
        VAR FirstPurchaseCustomers =
            ADDCOLUMNS (
                ALL ( Accounts[Account Number] ),
                "DateOfFirstBuy", CALCULATE ( MIN ( 'Amount Ordered'[Fiscal Week] ), ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] ) )
            )
        VAR NewAccts =
            FILTER (
                FirstPurchaseCustomers,
                CONTAINS ( VALUES ( 'Amount Ordered'[Fiscal Week] ), 'Amount Ordered'[Fiscal Week], [DateOfFirstBuy] )                
            )
        RETURN
            COUNTROWS (NewAccts)
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi jhenscheid1 ,

    Please try:

     

    New Acct =
VAR FirstPurchaseCustomers =
    ADDCOLUMNS (
        ALL ( Accounts[Account Number] ),
        "DateOfFirstBuy",
            CALCULATE (
                MIN ( 'Amount Ordered'[Fiscal Week] ),
                ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] )
            )
    )
VAR NewAccts =
    FILTER (
        FirstPurchaseCustomers,
        CONTAINS (
            FILTER (
                VALUES ( 'Amount Ordered'[Fiscal Week] ),
                'Amount Ordered'[Fiscal Week] > 9
            ),
            'Amount Ordered'[Fiscal Week], [DateOfFirstBuy]
        )
    )
RETURN
    COUNTROWS ( NewAccts )

     

    If this doesn't work, please feel free to contact me.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • jhenscheid1's avatar
      jhenscheid1
      Frequent Visitor
      3 years ago

      Hello Anonymous , thanks for the reply and input.  I think this is part of the solution.  By that I mean that I will need to filter data based on the fiscal week, but I also need to consider the "National" accounts, which are in a different table.  And I may not have been clear on what is needed.

       

      So, I need to include new accounts that are not National, but exclude the National accounts.  I tried to add that to the filter, but I am thinking because that column is in a different table, it will not work. 

       

      I tried something like:

      New Acct =
              VAR FirstPurchaseCustomers =
                  ADDCOLUMNS (
                      ALL ( Accounts[Account Number] ),
                      "DateOfFirstBuy", CALCULATE ( MIN ( 'Amount Ordered'[Fiscal Week] ), ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] ) )
                  )
              VAR NewAccts =
                  FILTER (
                      FirstPurchaseCustomers,
                      CONTAINS (
                          FILTER (
                              VALUES ( 'Amount Ordered'[Fiscal Week] ),
                              'Amount Ordered'[Fiscal Week] > 9 ,
                              FILTER('Sales Leads', 'Sales Leads'[Lead Type] <> "Nationwide Buy Group"),
                      )
                       'Amount Ordered'[Fiscal Week], [DateOfFirstBuy] )                
                  )
              RETURN
                  COUNTROWS (NewAccts)
       
      Hope that explains more of what I am looking to do.
       
      Thanks!
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi jhenscheid1 ,

        Please try:

        New Acct = 
VAR FirstPurchaseCustomers =
    ADDCOLUMNS (
        ALL ( Accounts[Account Number] ),
        "DateOfFirstBuy",
            CALCULATE (
                MIN ( 'Amount Ordered'[Fiscal Week] ),
                ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] )
            )
    )
VAR NewAccts =
    FILTER (
        FirstPurchaseCustomers,
        CONTAINS (
            CALCULATETABLE (
                VALUES ( 'Amount Ordered'[Fiscal Week] ),
                'Amount Ordered'[Fiscal Week] > 9,
                'Sales Leads'[Lead Type] <> "Nationwide Buy Group"
            ),
            'Amount Ordered'[Fiscal Week], [DateOfFirstBuy]
        )
    )
RETURN
    COUNTROWS ( NewAccts )

        If this doesn't work, can you show the relationship between 'Sales Leads' and 'Amount Ordered'? A sample file would be helpful. Thanks.

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data