Forum Discussion

Helper I

6 years ago

Solved

Look at other rows with same id in if statement

I have the following data in my query

Account ID	Adult/Child	Member Type	Booked	Booking Type
3214	Adult	Member
3214	Adult	Member
3214	Child	Member
3541	Adult	Member	Yes	Twin
3541	Adult	Member	Yes	Twin
3541	Child	Member	Yes
3678	Adult	Member	Yes	Double
3678	Adult	Associate Member	Yes	Double
3678	Child	Member	Yes
3985	Adult	Member	yes	twin
3985	Adult	Associate Member	Yes	Twin
4560	Adult	Member	Yes	single
4560	Adult	Member	Yes	Double
4985	Adult	Member	Yes	Twin
5894	Adult	Member	Yes	single
5894	Adult	Member
5894	Child	Member

For each account ID there may be multiple rows for each person in the account. I need the query to look at the other rows with the same ID to determine if the booking type is correct

An account with 2 adult members can have booking type: 'double' or 'twin'

An account with 1 adult member and 1 adult associate member can only have booking type 'double'

A child can not book or have a booking type

An account with 2 adults must have the same booking type for each adult

An account with 1 adult can only have booking type single

If the booking type is incorrect, I just want it to tell me that it needs to be checked so with this data the result would be

Account ID	Adult/Child	Member Type	Booking Type
3214	Adult	Member
3214	Adult	Member
3214	Child	Member
3541	Adult	Member	Twin
3541	Adult	Member	Twin
3541	Child	Member
3678	Adult	Member	Double
3678	Adult	Associate Member	Double
3678	Child	Member
3985	Adult	Member	CHECK BOOKING TYPE
3985	Adult	Associate Member	CHECK BOOKING TYPE
4560	Adult	Member	CHECK BOOKING TYPE
4560	Adult	Member	CHECK BOOKING TYPE
4985	Adult	Member	CHECK BOOKING TYPE
5894	Adult	Member	CHECK BOOKING TYPE
5894	Adult	Member	CHECK BOOKING TYPE
5894	Child	Member

I can do the if statement to check the booking type for each member but i can't work out how to reference the other rows with the same ID. They are shown here in sequential order but they might not actually be and there could be 1 -7 people in an account.

I would like to do this in powery query not DAX as not familiar with this at all.

Need Help

v-alq-msft

6 years ago

Hi, joooffice

Based on your description, I created data to reproduce your scneario.

Table:

You may create a measure as below.

Booking Type Measure = 
var _accountid=SELECTEDVALUE('Table'[Account ID])
var _adultchild=SELECTEDVALUE('Table'[Adult/Child])
var _membertype=SELECTEDVALUE('Table'[Member Type])
var _bookingtype=SELECTEDVALUE('Table'[Booking Type])
var _totalnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Booked]="Yes"
     )
)
var _adultnum =
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"
     )
)
var _adultmenbernum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Member Type]="Member"
     )
)

var _adultasmenbernum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Member Type]="Associate Member"
     )
)

var _chidnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Child"&&
         'Table'[Booked]="Yes"
     )
)
var _twinnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Booking Type]="Twin"
     )
)
var _doublenum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Booking Type]="Double"
     )
)
return
IF(
    SELECTEDVALUE('Table'[Booked])="Yes",
    IF(
        _adultchild="Adult",
        SWITCH(
            TRUE(),
            _adultnum=2&&OR(_twinnum=2,_doublenum=2)&&_adultmenbernum=1&&_adultasmenbernum=1&&_bookingtype="Double",_bookingtype,
            _adultnum=2&&OR(_twinnum=2,_doublenum=2)&&_adultnum=2&&_adultmenbernum=2&&_bookingtype in {"Double","Twin"},_bookingtype,
            _adultnum=1&&_bookingtype="Single",_bookingtype,
            "CHECK BOOKING TYPE"
        ),
        IF(
            _adultchild="Child",
            IF(
                _totalnum>_chidnum,
                _bookingtype,
                "CHECK BOOKING TYPE"
            ),
            "CHECK BOOKING TYPE"
        )
    )
)

Result:

Best Regards

Allan

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

Look at other rows with same id in if statement.pbix28 KB

3 Replies

artemus
Microsoft Employee
6 years ago
Group your table by AccountId, using the aggregate column of "All Rows". This column will contain a mini table for each AccountId. From here, you can filter the table inside your custom column and get the table count to determine how many of adults there are, or do another group and a count to determine if there are different booking types. When you are done adding your columns, expand the table column row to uncompress the table, this will copy the value you have in each custom column to every row it represents in the origional table.

v-alq-msft

Community Support

6 years ago

Hi, joooffice

Based on your description, I created data to reproduce your scneario.

Table:

You may create a measure as below.

Booking Type Measure = 
var _accountid=SELECTEDVALUE('Table'[Account ID])
var _adultchild=SELECTEDVALUE('Table'[Adult/Child])
var _membertype=SELECTEDVALUE('Table'[Member Type])
var _bookingtype=SELECTEDVALUE('Table'[Booking Type])
var _totalnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Booked]="Yes"
     )
)
var _adultnum =
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"
     )
)
var _adultmenbernum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Member Type]="Member"
     )
)

var _adultasmenbernum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Member Type]="Associate Member"
     )
)

var _chidnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Child"&&
         'Table'[Booked]="Yes"
     )
)
var _twinnum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Booking Type]="Twin"
     )
)
var _doublenum = 
COUNTROWS(
     FILTER(
         ALL('Table'),
         'Table'[Account ID]=_accountid&&
         'Table'[Adult/Child]="Adult"&&
         'Table'[Booked]="Yes"&&
         'Table'[Booking Type]="Double"
     )
)
return
IF(
    SELECTEDVALUE('Table'[Booked])="Yes",
    IF(
        _adultchild="Adult",
        SWITCH(
            TRUE(),
            _adultnum=2&&OR(_twinnum=2,_doublenum=2)&&_adultmenbernum=1&&_adultasmenbernum=1&&_bookingtype="Double",_bookingtype,
            _adultnum=2&&OR(_twinnum=2,_doublenum=2)&&_adultnum=2&&_adultmenbernum=2&&_bookingtype in {"Double","Twin"},_bookingtype,
            _adultnum=1&&_bookingtype="Single",_bookingtype,
            "CHECK BOOKING TYPE"
        ),
        IF(
            _adultchild="Child",
            IF(
                _totalnum>_chidnum,
                _bookingtype,
                "CHECK BOOKING TYPE"
            ),
            "CHECK BOOKING TYPE"
        )
    )
)

Result:

Best Regards

Allan

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

Look at other rows with same id in if statement.pbix28 KB

Anonymous

Not applicable

6 years ago

in the data_book query you have the grouby id scheme and the call of a cbt function to which the task of determining which booking types are ok and which are not.
you just have to complete the function to manage the various cases according to your rules.

data_book query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyNFHSUXJMKc0pAdK+qblJqUVAhgIYx+qQoMI5IzMnBYcKUxNDbGZEphYDyZDyzDwylGHYB1EGtdHM3AK3US75pUk5qdgUOhYX5ydnJpakKhDUgt9+SwtTbPZXgtWUwL2Cqgyn7XC/m5iaGeD2V3FmXjrUkfgVIvnGBIdL0Sw2tbDEmgwwLMalEJoaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account ID" = _t, #"Adult/Child" = _t, #"Member Type" = _t, Booked = _t, #"Booking Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID", Int64.Type}, {"Adult/Child", type text}, {"Member Type", type text}, {"Booked", type text}, {"Booking Type", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account ID"}, {{"all", each Table.AddColumn(_,"chk1", each cbt(_)) }, {"count", each Table.RowCount(_), type number}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Adult/Child", "Member Type", "Booked", "Booking Type", "chk1"}, {"Adult/Child", "Member Type", "Booked", "Booking Type", "chk1"})

in
    #"Expanded all"

function to be completed:

let 
 cbt= (tab) =>
 let
     bookingCat={"Double","Twin","Single"}, 
   booked= if Text.Lower(tab[Booked])="yes" and not List.Contains(bookingCat,tab[Booking Type]) then "check it!" else "ok"

// to be completed


in
    booked
in
cbt

Forum Discussion

Look at other rows with same id in if statement

3 Replies

Related content

OR Statement ?

SQL Statement opcion error

Support for MERGE statement

New Column with IF STATEMENT

IF Statement using Power Query