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 b...
Need Help
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