New table with corresponding columns

[Anonymous]

Hi all,

I have a table like this:

And I want a table like this:

In the left, we have that lines with "VENDIDO" status, and "ENTREGUE" in the right. Attention to column matching in "Data MD", "Bilhete", "Name" AND "Tipo de Venda" are equals; "Agência" is not necesary to coincide.

I have no idea how to do this in dax. Maybe a SUMMARIZE table with addons?

There is a minor error in the image above, in the right table, "ANDREA, ENTREGUE" should be "15/10/2018" as in the first table, sorry.

Anyone could help?

[v-cherch-msft]

Hi @Anonymous

It seems you may try to use SELECTCOLUMNS Function with condition as requested. As i'm not clear about the condition, show a sample as below for your reference.

Regards,

Cherie

[Anonymous]

Actually, my original table is huge, millions of rows and more than 20 columns.

Two of this columns are called "Type of sale" and "Status".

I need a new table with [Type of sale]="PTA" && [Status]="VENDIDO". Note: vendido is the word in portuguese for sold.

In [Status] there is two relevant values: "VENDIDO" e "ENTREGUE" - delivered in english.

So I have this:

PTA = 
    SELECTCOLUMNS (
        FILTER (
            DVENDAS; [Tipo Venda]="PTA" && [Status]="VENDIDO");
            "Bilhete"; DVENDAS[Bilhete];
            "Agência"; DVENDAS[Agência];
            "Bilheteiro"; DVENDAS[Logins.nome];
            "Data da compra"; DVENDAS[Data MD];
            "Linha"; DVENDAS[Linha];
            "Serviço"; DVENDAS[Serviço];
            "Passageiro"; DVENDAS[Passageiro];
            "Data do serviço"; DVENDAS[Data Servi.];
            "Valor cobrado"; DVENDAS[Vlr.Cobrado])

But now I need to insert new columns in this table that shows me when this ticket was delivered (with [Status]="ENTREGUE", near to this columns, into right - and idk how to do 😞

[Anonymous · Accepted solution]

i GOT IT!

With SELECTCOLUMNS table above, I hade created new columns using LOOKUPVALUE.

'DVENDAS" is the main table.

Data Entregue = LOOKUPVALUE('DVENDAS'[Data MD];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

Agência Entregue = LOOKUPVALUE('DVENDAS'[Agência];
'DVENDAS'[Bilhete];[Bilhete];
'DVENDAS'[Passageiro];[Passageiro];
'DVENDAS'[Data Servi.];[Data do serviço];
'DVENDAS'[Serviço];[Serviço];
'DVENDAS'[Status];"ENTREGUE")

Thanks guys for support.

[v-cherch-msft]

Hi @Anonymous

Glad to hear you've solved it, please accept your answer as solution, that way, other community members will easily find the solution when they get same issue. 

Regards,

Cherie

[tex628]

Yo mate, i believe that it's quite complicated to achieve what ur aiming for but i'll try and assist.

Due to the datamodel i believe that this is something that needs to be done in Power Query. 

This is what im thinking you should do:

Start by duplicating your initial query.

Next up, make two queries. one should hold the "VENDIDO" status, and one should hold "ENTREGUE" status. And of course all other fields that are relevant. 

The final step is a merge between the two queries. This requires that there is one key column that exists in both queries that connects the specific rows to eachother. It's also important that the relevant columns are renamed so that they arent merged together but stay divided. 

Hope tyhis gives a little help 🙂