customers who have made purchases within the last 90 days with minimal slicers.

[gbarr12345]

Hi everyone,

I have a bit of an awkward query that I’m getting an error with.

PBIX - https://drive.google.com/file/d/1jfkbcKp5-gvU-0WPPvdO9IuLrv_ZGHqY/view?usp=drive_link

I’m trying to create a Calculated table to show customers who have made purchases within the last 90 days.
I’m trying to alter the code below to include a few more rules and filters without the need to use slicers etc:

First, to put this code between a certain period (01/03/2024 to 31/05/2024 for example).

Second, to only include a certain product description (Iphone 15 or item code 5 only for example or any other product).

Third, to exclude STAFF from the Customer Class field in the Customer table.

Last, to only include Tesco from the Chain field in the customer table.

I know you can filter and use slicers, etc but my company are looking for set code that helps to not require slicers etc for the business users so they can see the data straight away.

The code I used below is getting an error - Too many arguments were passed to the AND function. The maximum argument count for the function is 2.

Any idea how to fix this? My code is below and sample PBIX ia attached also.

Any help would be greatly appreciated!

Customers with first purchase 90 Days =
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
'Sales Table',
'Sales Table'[Customer ID],
"MinTransactionDate" , MIN( 'Sales Table'[Transaction Date]) ,
"MaxTransactionDate" , MAX('Sales Table'[Transaction Date])
),

"1stTransactionOrNot" ,
IF( [MinTransactionDate] = [MaxTransactionDate] ,
"1st Transaction" , "Not the first Transaction")
),

"DaysFromFirstTransaction" ,
IFERROR( INT( TODAY() - [MaxTransactionDate] ),
0)
),

"RegularCustomerOrNot" ,
IF( [DaysFromFirstTransaction] <= 90 ,
"Regular Customer" , "Not a regular Customer" )
),

AND( [RegularCustomerOrNot] = "Regular Customer" ,
NOT ISBLANK( 'Sales Table'[Customer ID] ),
'Sales Table'[Transaction Date] >= DATE(2024, 3, 1)
&& 'Sales Table'[Transaction Date] <= DATE(2024, 5, 31),

('Sales Table'[Product Description] = "Iphone 15"
|| 'Sales Table'[Item Code] = 5),

'Customer Table'[Customer Class] <> "STAFF", 'Customer Table'[Chain] = "Tesco" ) )

[mark_endicott]

@gbarr12345 - If you would like to make this code more readable, I can suggest separating out all of the separate functions into VAR variables that will stage each element. See below:

VAR inital_table =
    CALCULATETABLE (
        SUMMARIZE (
            'Sales Table',
            'Sales Table'[Customer ID],
            "MinTransactionDate", MIN ( 'Sales Table'[Transaction Date] ),
            "MaxTransactionDate", MAX ( 'Sales Table'[Transaction Date] )
        ),
        'Sales Table'[Transaction Date] >= DATE ( 2024, 3, 1 ),
        'Sales Table'[Transaction Date] <= DATE ( 2024, 5, 31 ),
        NOT ISBLANK ( 'Sales Table'[Customer ID] ),
        'Sales Table'[Item Code] = 5,
        'Dimension_Customer Table'[Customer Class] <> "STAFF",
        'Dimension_Customer Table'[Chain] = "Tesco"
    )
VAR first_transaction =
    ADDCOLUMNS (
        inital_table,
        "1stTransactionOrNot",
            IF (
                [MinTransactionDate] = [MaxTransactionDate],
                "1st Transaction",
                "Not the first Transaction"
            )
    )
VAR days_from_first_transaction =
    ADDCOLUMNS (
        first_transaction,
        "DaysFromFirstTransaction", IFERROR ( INT ( TODAY () - [MaxTransactionDate] ), 0 )
    )
VAR regular_customer =
    ADDCOLUMNS (
        days_from_first_transaction,
        "RegularCustomerOrNot",
            IF (
                [DaysFromFirstTransaction] <= 90,
                "Regular Customer",
                "Not a regular Customer"
            )
    )
RETURN
    FILTER ( regular_customer, [RegularCustomerOrNot] = "Regular Customer" )

In this code, I have taken out the || part of your original sample, which should help you see what you need to do with the "Module sales with inventory" and "Dimension customer" filters

[gbarr12345]

Thank you for your response, much appreciated.

I'm getting the following error with the code:

90 days =

VAR Initial_Table =

    CALCULATETABLE(

        SUMMARIZE(

            'Module Sales with Inventory' ,

            'Module Sales with Inventory'[customer] ,

            "MinTransactionDate" , MIN( 'Module Sales with Inventory'[transactiondate] ) ,

            "MaxTransactionDate" , MAX( 'Module Sales with Inventory'[transactiondate] )

        ),

        'Module Sales with Inventory'[transactiondate] >= DATE( 2024, 3, 1) ,

        'Module Sales with Inventory'[transactiondate] <= DATE( 2024, 5, 31) ,

        NOT ISBLANK( 'Module Sales with Inventory'[customer] ) ,

        'Module Sales with Inventory'[item] = "SNNZ8090" ,

        'Dimension Customer'[customer class] <> "STAFF" ,

        'Dimension Customer'[chain] = "FSSI"

    )

VAR FirstTransaction =

    ADDCOLUMNS(

        Initial_Table ,

        "1stTransactionOrNot" ,

        IF(

            [MinTransactionDate] = [MaxTransactionDate] ,

            "1st Transaction" ,

            "Not the first transaction"

        )

    )

VAR DaysFromFirstTransaction =

    ADDCOLUMNS(

        FirstTransaction ,

        "DaysFromFirstTransaction" , IFERROR( INT( TODAY() - [MaxTransactionDate] ) , 0 )

    )

VAR RegularCustomer =

    ADDCOLUMNS(

        DaysFromFirstTransaction ,

        "Regular Customer or Not" ,

        IF(

            [DaysFromFirstTransaction] < 90 ,

            "Regular Customer" ,

            "Not a regular customer"

        )

    )

RETURN

    FILTER( RegularCustomer , [Regular Customer or Not] = "Regular Customer" )

[mark_endicott · Accepted solution]

@gbarr12345 - Ok I understand where the dependancies were coming from and have updated the code in your GB Test File and tested it with One-to-Many relationships with the Dimension_Customer Table & the Calendar (to MaxTransactionDate) both relationships are working. The change in the code below can be seen in each filter condiction within CALCULATETABLE(). 

VAR inital_table =
    CALCULATETABLE (
        SUMMARIZE (
            'Sales Table',
            'Sales Table'[Customer ID],
            "MinTransactionDate", MIN ( 'Sales Table'[Transaction Date] ),
            "MaxTransactionDate", MAX ( 'Sales Table'[Transaction Date] )
        ),
        FILTER (
            ALLNOBLANKROW ( 'Sales Table'[Transaction Date] ),
            'Sales Table'[Transaction Date] >= DATE ( 2024, 3, 1 )
        ),
        FILTER (
            ALLNOBLANKROW ( 'Sales Table'[Transaction Date] ),
            'Sales Table'[Transaction Date] <= DATE ( 2024, 5, 31 )
        ),
        FILTER (
            ALLNOBLANKROW ( 'Sales Table'[Customer ID] ),
            NOT ( ISBLANK ( 'Sales Table'[Customer ID] ) )
        ),
        FILTER (
            ALLNOBLANKROW ( 'Sales Table'[Item Code] ),
            'Sales Table'[Item Code] = 5
        ),
        FILTER (
            ALLNOBLANKROW ( 'Dimension_Customer Table'[Customer Class] ),
            'Dimension_Customer Table'[Customer Class] <> "STAFF"
        ),
        FILTER (
            ALLNOBLANKROW ( 'Dimension_Customer Table'[Chain] ),
            'Dimension_Customer Table'[Chain] = "Tesco"
        )
    )
VAR first_transaction =
    ADDCOLUMNS (
        inital_table,
        "1stTransactionOrNot",
            IF (
                [MinTransactionDate] = [MaxTransactionDate],
                "1st Transaction",
                "Not the first Transaction"
            )
    )
VAR days_from_first_transaction =
    ADDCOLUMNS (
        first_transaction,
        "DaysFromFirstTransaction", IFERROR ( INT ( TODAY () - [MaxTransactionDate] ), 0 )
    )
VAR regular_customer =
    ADDCOLUMNS (
        days_from_first_transaction,
        "RegularCustomerOrNot",
            IF (
                [DaysFromFirstTransaction] <= 90,
                "Regular Customer",
                "Not a regular Customer"
            )
    )
RETURN
    FILTER ( regular_customer, [RegularCustomerOrNot] = "Regular Customer" )

Relationships are working without a circular dependency: 

If this works for you please mark it as the solution. 

[mark_endicott]

@gbarr12345 - Did this work for you now? I've spent a fair amount of time on this for you, so I'd appreciate it if you can mark it as the solution, this also helps other users find how we've fixed this stage by stage. 

Thanks!

[gbarr12345]

Apologies Mark, I was out of office until this morning so only saw it when I came in.

I tried this on different sample data I had and it seems to have worked. It must have been an issue with my other data.

Appreciate the help and I'll mark it as a solution and provide a kudos of course!

[mark_endicott]

@gbarr12345 - No problem, the new code will work becuase I wrapped all of the Filters with an ALLNOBLANKROW(). The filter conditions were previously using an ALL() under the hood to clear filters then reset them to the value given. But ALL() inserts a blank row into the relationship to account for NULLS. It's this blank row that causes the circular dependancy. So ALLNOBLANKROW prevents this being created. 

It's a pretty complex thing to get your head around. 

Glad this is now resolved for you.

[gbarr12345]

No problem at all, still new to Power BI so lots of new things to wrap my head around.

Really appreciate your time and effort, you've been a great help!

[mark_endicott]

@gbarr12345 - No problem, there's a link to my LinkedIn on my profile page on here, I post lots of hints and tips about Power BI amoungst other things, and I'm a consultant, so if you or your organisation need any help adopting Power BI, you can give me a shout on there. 

[gbarr12345]

Oh perfect, thank you so much! I'll be sure to give you a connect. Thank you so much!

[mark_endicott]

@gbarr12345 - I am not able to replicate this error, the code I have supplied works fine in the GB Test file you originally linked. 

This error can only be to do with the relationships you need to set up, do you get the error if you remove all of the relationships to the calculated table?

If this clears the error, it will be a tricky one for me to resolve, as I need to understand where all the dependencies are coming from. 

I'll respond as soon as I can, but please do let me know if removing the relationships fixes the error. 

[mark_endicott]

@gbarr12345 - The "Module sales with inventory" table filter can be moved to the CALCULATETABLE filters along with the filters for 'transactiondate' and 'customer', if there is a one-to-many relationship from your "Dimension customer" table (one) to the "Module sales with inventory" table (many), you should also be able to move these filters too. 

If you try this and get any sort of error to do with exceeding the maximum amount of filters, you can try swapping out the commas for "&" as I have done at the bottom. 

[gbarr12345]

Hi Mark,

Thank you for your response.

Apologies, is it possible to show me the code for that as I'm looking at it and not fully sure where and how to make the changes?

[mark_endicott]

@gbarr12345 - As you have only sent me a screenshot of your new code, I'll have to show you using my sample and you'll have to map it to your table names, give this a try:

FILTER (
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Sales Table',
                        'Sales Table'[Customer ID],
                        "MinTransactionDate", MIN ( 'Sales Table'[Transaction Date] ),
                        "MaxTransactionDate", MAX ( 'Sales Table'[Transaction Date] )
                    ),
                    'Sales Table'[Transaction Date] >= DATE ( 2024, 3, 1 ),
                    'Sales Table'[Transaction Date] <= DATE ( 2024, 5, 31 ),
                    NOT ISBLANK ( 'Sales Table'[Customer ID] ),
                    ( 'Sales Table'[Country Label] = "Iphone 15"
                        || 'Sales Table'[Item Code] = 5 ),
                    'Dimension_Customer Table'[Customer Class] <> "STAFF",
                    'Dimension_Customer Table'[Chain] = "Tesco"
                ),
                "1stTransactionOrNot",
                    IF (
                        [MinTransactionDate] = [MaxTransactionDate],
                        "1st Transaction",
                        "Not the first Transaction"
                    )
            ),
            "DaysFromFirstTransaction", IFERROR ( INT ( TODAY () - [MaxTransactionDate] ), 0 )
        ),
        "RegularCustomerOrNot",
            IF (
                [DaysFromFirstTransaction] <= 90,
                "Regular Customer",
                "Not a regular Customer"
            )
    ),
    [RegularCustomerOrNot] = "Regular Customer"
)

You'll see I've had to use "Country Label" instead of "Product Description" as the latter does not exist in your sample "Sales Table", but despite this the code does produce one record that seems to fit your filters:

This shows that moving all your filters except [RegularCustomerOrNot] to the CALCULATETABLE will work and they can be comma separated. 

[mark_endicott]

@gbarr12345 - Using your sample file, I have ammended your code to the below, however I cannnot test it with all the filters because Product Description and the Customer Table do not exist. 

It appears to be working when I remove the filters for those columns (see screenshot below DAX).

It is likely that you could also move some of these filters to my CALCULATETABLE further up the chain, but that would need testing too. 

FILTER (
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Sales Table',
                        'Sales Table'[Customer ID],
                        "MinTransactionDate", MIN ( 'Sales Table'[Transaction Date] ),
                        "MaxTransactionDate", MAX ( 'Sales Table'[Transaction Date] )
                    ),
                    'Sales Table'[Transaction Date] >= DATE ( 2024, 3, 1 ),
                    'Sales Table'[Transaction Date] <= DATE ( 2024, 5, 31 ),
                    NOT ISBLANK ( 'Sales Table'[Customer ID] )
                ),
                "1stTransactionOrNot",
                    IF (
                        [MinTransactionDate] = [MaxTransactionDate],
                        "1st Transaction",
                        "Not the first Transaction"
                    )
            ),
            "DaysFromFirstTransaction", IFERROR ( INT ( TODAY () - [MaxTransactionDate] ), 0 )
        ),
        "RegularCustomerOrNot",
            IF (
                [DaysFromFirstTransaction] <= 90,
                "Regular Customer",
                "Not a regular Customer"
            )
    ),
    [RegularCustomerOrNot] = "Regular Customer"
    && ( 'Sales Table'[Product Description] = "Iphone 15"
                || 'Sales Table'[Item Code] = 5 )
                && 'Customer Table'[Customer Class] <> "STAFF"
                && 'Customer Table'[Chain] = "Tesco"   
)

Please give this a try and let me know if it works. 

[gbarr12345]

Hi Mark,

Thank you very  much for your response.

I gave it a try but it seems to have errored at the end. Any idea how to fix it?