Forum Discussion

helohandrade's avatar
helohandrade
Regular Visitor
8 years ago

Hlookup with calculated columns

Hello all.   I'm trying to make a hlookup with calculated columns in PowerBI, but I still can not figure out how.   All columns in the table are calculated except "UF Descrição". The "maior envio...
helohandrade's avatar
helohandrade
Regular Visitor
8 years ago

Hello!

 

Anonymous sure!

 

PBI imported spreadsheet sample: https://www.4shared.com/s/fh9pCTKN_ee

 

another print of the table that I did in excel: 

 

 

And there is a print of the sample of table that I imported in Power BI:

 

 

 

Thanks again!

Anonymous's avatar
Anonymous
Not applicable
8 years ago

helohandrade,

Please share the Excel files via Dropbox or OneDrive. I am unable to download the file from the above link.

Regards,
Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      helohandrade,

      I am unable to find the calculated columns you mentioned in the sample Excel file. How do you create these calculated columns in Power BI Desktop?

      Regards,
      Lydia

      • helohandrade's avatar
        helohandrade
        Regular Visitor
        8 years ago

        Hi.

        All the Cont's I made with countrows like

        "Cont Jamef = COUNTROWS(FILTER(Compilados;Compilados[Transportadora] = "jamef")))

         

        And maior envio I made with several max

        maior envio = MAX(Compilados[Cont Diálogo];MAX(Compilados[Cont Jad];MAX(Compilados[Cont Jamef];MAX(Compilados[Cont LBR];MAX(Compilados[Cont Nowlog];MAX(Compilados[Cont Plimor];MAX(Compilados[Cont TNT];MAX(Compilados[Cont TNT];MAX(Compilados[Cont Total Express];(Compilados[Cont Transfolha]))))))))))

         

        When I pull the value of "Transportadora" to the table, "maior envio" loses meaning, because the table lists all shipments of all carriers.
        I just want the name of the carrier that has the largest number of shipments in the period for the state in the first column, in the way that "UF Descrição" do not become duplicate.

        So it has to appear:

         

        Acre 26 Jadlog

        Alagoas 79 Jadlog

        Amapa 12 Jadlog

        Amazonas 81 Jadlog

        Bahia 423 TNT

         

        etc.

         

        Thanks