Canada customers who haven't purchased 19AU1000 measure

[gbarr12345]

Hi everyone,

I'm trying to create a measure to find out what customers from Canada haven't purchased a product code 19AU1000 in a certain period of time.

There are various tables involved in the measure but they are all connected correctly.

I have the code below. Can any of ye see where I'm going wrong?

Canada cust who haven't bought 19AU1000 =

VAR CanadaCustomers =

    CALCULATETABLE(

        VALUES('Dimension Customer'[description]),

        'Dimension Market'[market] = "CAN",

        'Dimension Item'[item] = "19AU1000",

        'Module Sales with Inventory'[Period Date] >= DATE(2023, 1, 31),

        'Module Sales with Inventory'[Period Date] <= DATE(2023, 03, 30)

    )

VAR PurchasedCustomers =

    CALCULATETABLE(

        VALUES('Dimension Customer'[description]),

        'Dimension Item'[item] = "19AU1000"

    )

RETURN

    IF(

        COUNTROWS(

            INTERSECT(

                CanadaCustomers,

                PurchasedCustomers

            )

        ) = 0,

        "No purchase",

        BLANK()

    )

[v-jtian-msft]

Hi,@gbarr12345,I am glad to help you. @danextian ,thank you very much for your help with this case  and I would like to share my understanding below.
According to your description, are you trying to filter out Canadian users who do not purchase 19AU1000 metrics within the filtered time period, since there is no way to know the relevant structure of your data right now, I am now modifying the code you provided.
If my understanding is correct, you can refer to my modified code below.

Canada cust who haven't bought 19AU1000 =
VAR CanadaCustomers =
    CALCULATETABLE (
        VALUES ( 'Dimension Customer'[description] ),
        'Dimension Market'[market] = "CAN",
        'Dimension Item'[item] = "19AU1000",
        'Module Sales with Inventory'[Period Date] < DATE ( 2023, 1, 31 )
            || 'Module Sales with Inventory'[Period Date] > DATE ( 2023, 03, 30 )
    )
VAR PurchasedCustomers =
    CALCULATETABLE (
        VALUES ( 'Dimension Customer'[description] ),
        'Dimension Item'[item] = "19AU1000"
    )
RETURN
    IF (
        COUNTROWS ( INTERSECT ( CanadaCustomers, PurchasedCustomers ) ) = 0,
        "No purchase",
        BLANK ()
    )

The changes I made were mainly in “CanadaCustomers”: I changed the filter to exclude Canadian customers who purchased "19AU1000" between 2023, 1, 31 and 2023, 03, 30.
Would you be able to provide me with your specific data, or if not, could you provide me with the fields and relationships between the tables involved? This would be very helpful in solving your problem.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi Carson,

Essentially I'm looking to do the opposite. I'm trying to only filter on Canada Customers who have not purchased 19AU1000.

Unfortunately I cannot share the data fields but I have a screenshot of the main relationships below if that would help?

Many Thanks for your response.

[gbarr12345]

As well as that, the results I'm looking for are showing blank. 

Is there a small issue with the code you provided me?

[gbarr12345]

Actually now I'm getting the below error:

Is it possible you could help me write a DAX measure to only show the Canada customers who haven't purchased 19AU1000 please as my measure doesn't seem to be working at all.

[v-jtian-msft]

Hi,@gbarr12345 

I am glad to help you.
I am sorry to see that you are getting this error after modifying your original measure, the message says that you cannot compare a text type value with a numeric value, I have seen your model relationships and the relationships are complex, so it would be more beneficial if you provide some non-sensitive data so that the problem can be solved.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi Carson,

Please find attached the google drive link to the sample data PBIX that I created based on the same tables and fields from the main data.

Hopefully this is more helpful for you to look into this issue.

https://drive.google.com/file/d/1oAMGapVUty-exSWIfGtDok1XmsI54E3R/view?usp=drive_link

Many Thanks,

Greg

[Ashish_Mathur]

Access Denied message.

[gbarr12345]

Apologies, please try now.

[Ashish_Mathur]

Share the download link of this Excel file - Power BI sample data_Gbarr.xlsx

[gbarr12345]

https://docs.google.com/spreadsheets/d/1CDrxlk_MwnGi4cx0x1k2YIr_hDQM4Vzv/edit?usp=drive_link&ouid=11...

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  Please check.

[gbarr12345]

Hi Ashish,

That measure seems to work, thank you very much!

Just one quick question. Is there a way to put the actual description into the measure without having to use slicers?

For example, 'Keyboards'

The goal for this measure for me is to have the users not need to use slicers or filters and make it easily accessible for them.

For example, 

[Ashish_Mathur]

You are welcome.  Slicers make thing simple/dynamic.  Let it stay.

[gbarr12345]

Also, I've updated the Excel Data i nthe XLS file to have more specific products now which may help.

[danextian]

Hi @gbarr12345 

Based on your formula logic, you're trying to compare CAN customers who bought 19AU1000  in 2023 Q1 against customers against all customers who did. I don't think that's what you want.

I would return those variables as a calculated table to see what's going on.