Rookie Measure Question

[RichOB]

Hi, if the table below is my dataset and I want to find how many customers purchased just a bike, what measure will show me that there were 4 bikes sold in April, please?

Date	Customer	Item
01/04/2024	1	Bike
01/04/2024	2	Bike
01/04/2024	3	Bike, Helmet, Gloves
01/04/2024	4	Helmet
01/04/2024	5	Bike, Helmet, Shoes, Gloves

Thanks

[Ashish_Mathur]

Hi,

My suggestion is to split the second column by rows in the Power Query Editor.  Thereafter, jsut write this measure

Measure = distinctcount(Data[Customer])

Hope this helps.

[Anonymous · Accepted solution]

Thanks for the reply from Kedar_Pande, please allow me to provide another insight.
Hi @RichOB ,

Please try the following measure.

Number of bikes sold in April = 
COUNTX (
    FILTER (
        'Table',
        CONTAINSSTRING ( 'Table'[Item], "Bike" )
            && MONTH ( 'Table'[Date] ) = 4
    ),
    'Table'[Customer]
)

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[Kedar_Pande]

@RichOB 

To calculate how many customers purchased just a bike in April from your dataset

JustBikeSales = 
CALCULATE(
COUNTROWS('Table'),
'Table'[Item] = "Bike",
MONTH('Table'[Date]) = 4
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn