March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello Experts,
I need to create a card that show the Count of Customers with no Sales filtered by date and brand. I tried the following measure
Sales Invoice | |||
Customer ID | Total Amount | Date | Sales Invoice ID |
A | 100 | 43101 | ID1 |
B | 200 | 43133 | ID2 |
C | 300 | 43134 | ID3 |
D | 400 | 43194 | ID4 |
Invoice Line | ||
Invoice Line ID | Sales Invoice ID | Brand |
LINE1 | ID1 | Brand A |
LINE2 | ID2 | Brand B |
LINE3 | ID3 | Brand A |
LINE4 | ID4 | Brand B |
LINE5 | ID1 | Brand A |
LINE6 | ID2 | Brand B |
LINE7 | ID3 | Brand A |
LINE8 | ID4 | Brand B |
Customer |
Customer ID |
A |
B |
C |
D |
E |
Solved! Go to Solution.
You may create the measures as below and change the relationships. Attached the sample file.
Measure = IF ( NOT ( MAX ( Customer[Customer ID] ) ) IN VALUES ( 'Sales invoice'[Customer ID] ), 1 )
Customers with no sales = SUMX(Customer,[Measure])
Regards,
Cherie
Hi,
Based on the data you have shared, please let us know the exact result you are expecting and rationale for the same.
You may create the measures as below and change the relationships. Attached the sample file.
Measure = IF ( NOT ( MAX ( Customer[Customer ID] ) ) IN VALUES ( 'Sales invoice'[Customer ID] ), 1 )
Customers with no sales = SUMX(Customer,[Measure])
Regards,
Cherie
Hi, this works when comparing all customers in the customers table however I need to filter out the customers table to consider only the customers who have bought in the last one year. How can I go around this?
Could you show us the measure [Count of Sales invoice] you created? Please show the relationships. If you could share the .pbix file, we could provide an accurate solution. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
This is my sample data in pbix file
https://drive.google.com/drive/folders/1A6sS4WCkFsBHwVgYI3JzGHVmuAE5hhw-?usp=sharing
Thanks
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
90 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |