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

Customers from Canada not staff with 1 purchase measure

Hi everyone,

 

I wrote a measure that works that shows what customers from Canada bought a certain product between a certain time period.

 

I'm trying to alter the code even further to exclude the STAFF customer class. Any idea of the best additional code to put in to make it work?

 

The table for the Customer class field is Customer table.

 

Any help would be appreciated!

 

Code below:

 

Canada Item 1 =
CALCULATE(
    COUNTROWS('Sales Table'),
    FILTER(
        'Sales Table',
        'Sales Table'[Market] = "CAN" &&
        'Sales Table'[Item Code] = 1 &&
        'Sales Table'[Transaction Date] >= DATE(2024, 1, 1) &&
        'Sales Table'[Transaction Date] <= DATE(2024, 5, 31)
    )
)
1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @Ritaf1983 ,

Thanks fo your quick response.

Hi @gbarr12345 ,

I can not get the pbix file. Please have a try.

Canada Item 1 = 
CALCULATE(
    COUNTROWS('Sales Table'),
    FILTER(
        'Sales Table',
        'Sales Table'[Market] = "CAN" &&
        'Sales Table'[Item Code] = 1 &&
        'Sales Table'[Transaction Date] >= DATE(2024, 1, 1) &&
        'Sales Table'[Transaction Date] <= DATE(2024, 5, 31)
    ),
    FILTER(
        'Customer',
        NOT('Customer'[Customer Class] = "STAFF")
    )
)

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

View solution in original post

4 REPLIES 4
v-rongtiep-msft
Community Support
Community Support

Hi @Ritaf1983 ,

Thanks fo your quick response.

Hi @gbarr12345 ,

I can not get the pbix file. Please have a try.

Canada Item 1 = 
CALCULATE(
    COUNTROWS('Sales Table'),
    FILTER(
        'Sales Table',
        'Sales Table'[Market] = "CAN" &&
        'Sales Table'[Item Code] = 1 &&
        'Sales Table'[Transaction Date] >= DATE(2024, 1, 1) &&
        'Sales Table'[Transaction Date] <= DATE(2024, 5, 31)
    ),
    FILTER(
        'Customer',
        NOT('Customer'[Customer Class] = "STAFF")
    )
)

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

Hi,

 

Thank you for your help.

 

I'm happy to say that this measure worked for me so thank you so much!

Ritaf1983
Super User
Super User

Hi @gbarr12345 
If you have a standard star schema model you can use the measure like :
Excluded staff = calculate([Canada Item 1 ], 'customers'[class]<>"Staff")

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

I tried the excluded staff measure and it doesn't seem to have worked.

 

I've the PBIX attached here also and the code is below - https://drive.google.com/file/d/10NhfdOt2WkPD3Rf6_p3Ngi9GXVd6QJbh/view?usp=drive_link

 

gbarr12345_0-1716431416529.png

 

gbarr12345_1-1716431427529.png

 

 

 

Canada Item 2 =
CALCULATE(
    COUNTROWS('Sales Table'),
    FILTER(
        'Sales Table',
        'Sales Table'[Market] = "CAN" &&
        'Sales Table'[Item Code] = 2 &&
        'Sales Table'[Transaction Date] >= DATE(2024, 1, 1) &&
        'Sales Table'[Transaction Date] <= DATE(2024, 5, 31)
    )
)

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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.