DAX: How to duplicate a row based on a number in one of the columns and give it a semi-related value

[Anonymous]

I have a table (simplified) like this:

Booking #	Ticket Type	Ticket Count
12111	Adult	1
12111	Child	2
12111	Package	0

And a table of tickets like this:

Booking ID	TicketID	Ticket Type
12111	12345	Adult
12111	12346	Child
12111	12347	Child

I need to be able to output something like this:

Booking ID	Ticket ID	Ticket Type
12111	12345	Adult
12111	12346	Child
12111	12347	Child
12111		Package

[v-xiaotang · Accepted solution]

Hi @Anonymous 

try this measure

Append = 
VAR tab =
    UNION (
        tickets,
        SELECTCOLUMNS (
            'Table',
            "Booking ID", 'Table'[Booking #],
            "Ticket ID", BLANK (),
            "Ticket Type", 'Table'[Ticket Type]
        )
    )
RETURN
    FILTER (
        tab,
        [TicketID] <> BLANK ()
            || (
                [TicketID] = BLANK ()
                    && NOT ( [Ticket Type] IN VALUES ( tickets[Ticket Type] ) )
            )
    )

result:

Best Regards,

Community Support Team _ Tang

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

[amitchandak]

@Anonymous , Create a combine key

Key = [Booking ID] & [Ticket Type]

In both table and  join them on the key .

There two options. In visual you can use the option to show items with no data.

Or you can merge them in power query using left join. In case the above column you also need to create in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

I need a DAX solution unfortunately as I'm using directquery.