Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gbarr12345
Post Patron
Post Patron

Canada customers who haven't purchased 19AU1000 measure

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?

 

gbarr12345_0-1714619079583.png

 

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()
    )

 

1 ACCEPTED SOLUTION

Hi,

PBI file attached.  Please check.

Ashish_Mathur_0-1715341358717.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

15 REPLIES 15
v-jtian-msft
Community Support
Community Support

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.


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_0-1714694319908.png

 

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_0-1714942597283.png

 

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.

 

gbarr12345_1-1714943882843.png

 

gbarr12345_2-1714943938595.png

 

 

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.

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

Access Denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Apologies, please try now.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

PBI file attached.  Please check.

Ashish_Mathur_0-1715341358717.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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, 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors