Help with relationships - customers & bookings

[Anonymous]

Hi Everyone

I am looking for some help to create a query. I suspect my problem is in the definition of the relationships but I can't see what I've done wrong.

Relationships

I have 2 tables, customers and bookings.

Both tables have a field "fullname"

I have defined a many-to-many relationship with customers filtering bookings.

PowerPi did not auto detect this relationship, I had to set it up manually.

additional fields relevant to query

The customers table has a whole number field called "total bookings"

The bookings table has a text field called "bookingtype" where the value can be 'term', 'taster' or 'special'

what I am trying to do

I am trying to count the number of customers who only made 1 booking and that booking was of type taster.

I tried to do this with a card visualisation as follows:

fields: count of customers.totalbookings

filter:

• bookings.bookingtype = taster
• customers.totalbookings = 1

but this doesn't work.

Can you help?

[v-lid-msft]

Hi @Anonymous ,

We can try to create a measure using following formula to meet your requirement:

CountMeasure =
COUNTX (
    FILTER ( 'Customers', [totalbookings] = 1 ),
    VAR name = [fullname]
    RETURN
        IF (
            CALCULATE (
                COUNTROWS ( 'bookings' ),
                FILTER ( 'bookings', [fullname] = name && [bookingtype] = "taster" )
            ) > 0,
            1,
            BLANK ()
        )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[venal]

@Anonymous 

Can you please share the sample data and let us know the common column from the 2 tables to re-pro the issue from our end.

If possible please share the expected result.

[Anonymous]

Hello

I attached example data. I couldn't see how to upload the files so have copied in and few rows.

I have removed real names etc but hopefully this is sufficient to help you see my problem

With the data below I would expect the result of the query to be 4. customers 1,3,6 and 9 each have totalbookings = 1 and their bookings are of type taster.

customers table

Last name	First name	Created	Total bookings	Cancellations	No-shows	fullname
lastname1	firstname1	19/09/2017	1	0	1	firstname1 lastname1
lastname2	firstname2	06/07/2017	0	0	0	firstname2 lastname2
lastname3	firstname3	09/04/2018	1	0	0	firstname3 lastname3
lastname4	firstname4	04/06/2018	2	0	0	firstname4 lastname4
lastname5	firstname5	06/10/2017	3	0	0	firstname5 lastname5
lastname6	firstname6	15/11/2018	1	0	0	firstname6 lastname6
lastname7	firstname7	12/11/2017	4	0	0	firstname7 lastname7
lastname8	firstname8	23/11/2017	2	0	1	firstname8 lastname8
lastname9	firstname9	18/06/2019	1	0	0	firstname9 lastname9
lastname10	firstname10	06/07/2017	0	0	0	firstname10 lastname10
lastname11	firstname11	06/07/2017	0	0	0	firstname11 lastname11
lastname12	firstname12	30/07/2017	4	0	1	firstname12 lastname12
lastname13	firstname13	06/01/2019	3	0	0	firstname13 lastname13
lastname14	firstname14	01/09/2017	1	0	0	firstname14 lastname14
lastname15	firstname15	04/08/2018	2	0	0	firstname15 lastname15
lastname16	firstname16	06/07/2017	5	0	3	firstname16 lastname16
lastname17	firstname17	24/06/2019	1	0	0	firstname17 lastname17
lastname18	firstname18	06/07/2017	0	0	0	firstname18 lastname18
lastname19	firstname19	12/09/2019	4	0	0	firstname19 lastname19

bookings table

Booking number

Start

End

First name (customer)

Last name (customer)

Class

Locations for Tasters

Venues for Blocks

Product code

Private event

Status

Booking status

fullname

classtype

254808192867224

27/8/2018 15:45

########

firstname1

lastname1

Big Builders Edinburgh (Ages 3 to 5)  Term 1

Broxburn Free Church

32546K9YAU15FF8D1AA94

FALSE

normal

Customer attended

firstname1 lastname1

taster

254807102709071

27/8/2018 15:45

########

firstname2

lastname2

Big Builders Taster Classes Edinburgh (Ages 3 to 5)

Stockbridge Lifecare Centre

325499NPL9160AD068F9D

FALSE

normal

Customer attended

firstname2 lastname2

term

254807106889300

27/8/2018 15:45

########

firstname3

lastname3

Big Builders Taster Classes Edinburgh (Ages 3 to 5)

Stockbridge Lifecare Centre

325499NPL9160AD068F9D

FALSE

normal

Customer attended

firstname3 lastname3

taster

254807311204385

27/8/2018 15:45

########

firstname4

lastname4

Big Builders Edinburgh (Ages 3 to 5)  Term 1

Broxburn Free Church

32546K9YAU15FF8D1AA94

FALSE

normal

Customer attended

firstname4 lastname4

term

254808278351439

27/8/2018 15:45

########

firstname5

lastname5

Big Builders Taster Classes Edinburgh (Ages 3 to 5)

Stockbridge Lifecare Centre

325499NPL9160AD068F9D

FALSE

normal

Customer attended

firstname5 lastname5

term

254807153999138

27/8/2018 15:45

########

firstname6

lastname6

Big Builders Edinburgh (Ages 3 to 5)  Term 1

Broxburn Free Church

32546K9YAU15FF8D1AA94

FALSE

normal

Customer attended

firstname6 lastname6

taster

254808052548098

27/8/2018 15:45

########

firstname7

lastname7

Big Builders Edinburgh (Ages 3 to 5)  Term 1

Broxburn Free Church

32546K9YAU15FF8D1AA94

FALSE

normal

Customer attended

firstname7 lastname7

term

254808276788706

27/8/2018 16:00

########

firstname8

lastname8

Big Builders Taster Classes Fife (Ages 3 to 5)

Dalgety Bay

3254RL7RHP162192BB5AC

FALSE

normal

Customer attended

firstname8 lastname8

term

254807215779378

27/8/2018 16:00

########

firstname9

lastname9

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer didn't turn up

firstname9 lastname9

taster

254807216964967

27/8/2018 16:00

########

firstname10

lastname10

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer didn't turn up

firstname10 lastname10

term

254808039461583

27/8/2018 16:00

########

firstname11

lastname11

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer didn't turn up

firstname11 lastname11

term

254807134097890

27/8/2018 16:00

########

firstname12

lastname12

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer attended

firstname12 lastname12

term

254808147290237

27/8/2018 16:00

########

firstname13

lastname13

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer didn't turn up

firstname13 lastname13

term

254808147290237

27/8/2018 16:00

########

firstname14

lastname14

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer didn't turn up

firstname14 lastname14

term

254807192133323

27/8/2018 16:00

########

firstname15

lastname15

Big Builders Fife (Ages 3 to 5) Term 1

Juniper Green

32547MXCTK16215684BCC

FALSE

normal

Customer attended

firstname15 lastname15

term

254808265992767

27/8/2018 16:45

########

firstname16

lastname16

Galileo Fife Term - 2

Dalgety Bay

3254CFLPHT163F4A70AEE

FALSE

normal

Customer told us they weren't coming

firstname16 lastname16

term

254808132839003

27/8/2018 16:45

########

firstname17

lastname17

Bricks Challenge Tasters Fife (Ages 6 to 😎

Dalgety Bay

3254CCR4AM164F5B1F8F7

FALSE

normal

Customer attended

firstname17 lastname17

term

254807167484563

27/8/2018 16:45

########

firstname18

lastname18

Bricks Challenge  - Edinburgh Term 1

Broxburn Free Church

3254Y9WT7A163EA8445DC

FALSE

normal

Customer attended

firstname18 lastname18

term

254807061271077

27/8/2018 16:45

########

firstname19

lastname19

Bricks Challenge West Lothian Term 1

3254UK3HWP15FF8CE43EE

FALSE

canceled

firstname19 lastname19

term

Thank you for your help.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following formula to meet your requirement based on your sample data:

CountMeasure = 
COUNTX (
    FILTER ( 'Customers', [total bookings] = 1 ),
    VAR n = [fullname]
    RETURN
        IF (
            CALCULATE (
                COUNTROWS ( 'bookings' ),
                FILTER ( 'bookings', [fullname] = n && [Classtype] = "taster" )
            ) > 0,
            1,
            BLANK ()
        )
)

Best regards,

[Anonymous]

That's great! Thank you for your help.