Forum Discussion

RafaelRSantos_'s avatar
RafaelRSantos_
Frequent Visitor
4 years ago
Solved

Problem to show LOOKUPVALUE

Hi guys, i'm facing a problem to show my LOOKUPVALUE in Matrix Visualization.

I have an account number, eg: 1, 1.1, 1.1.1, 1.1.2

I used PATH function 

 

 

 

Caminho = PATH('Contabil DimContaContabil'[Conta],'Contabil DimContaContabil'[ContaSuperior])

 

 

 

 

Path function result

 

 

and than i used LOOKUPVALUE to create hierarchy

First level

 

 

 

Conta N1 = LOOKUPVALUE('Contabil DimContaContabil'[ContaFormatada],'Contabil DimContaContabil'[Conta],PATHITEM('Contabil DimContaContabil'[Caminho],1))

 

 

 

 

Second level

 

 

 

Conta N2 = LOOKUPVALUE('Contabil DimContaContabil'[ContaFormatada],'Contabil DimContaContabil'[Conta],PATHITEM('Contabil DimContaContabil'[Caminho],2))

 

 

 

 

until seventh level.

I have some data showing seventh level, but some data exists in 5th level or 4th or 6th, etc, and when i filter to not show blank values the result is not what i expected.

The picture below show a data that exists in 5th level

 

 

Thank for help

Need Help

5 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi RafaelRSantos_ 

    Thanks for reaching out to us.

    Could you share some relevant sample data and the expected output? Not sure what's wrong with the picture below

     

    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.

    • RafaelRSantos_'s avatar
      RafaelRSantos_
      Frequent Visitor
      4 years ago

      Hi v-xiaotang ,

      thanks for help, i attached into this reply some data from this problem.

      The FactTable.csv have the account and the sum of debt and credit values.

      Header(Formated Account; Account; Debt; Credit)

      The DimTable.csv have a hierachy of account 1123065.

      Header(Account; Formated Account; Up Account; Level)

      The DimTable2.csv have all account where account 1 is the head account.

      Header(Account; Formated Account; Up Account; Level)

      Files 

      Thanks fo help me

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi RafaelRSantos_ 

        Thanks for your reply.

        >> I have some data showing seventh level, but some data exists in 5th level or 4th or 6th, etc, and when i filter to not show blank values the result is not what i expected.

        So the problem you encountered is that the data of the seventh level went to other levels, and when you did not select blank in the filter, the table was empty. Actually it should show some non-null values, right?

         

        Besides, the file you attached has been deleted. And I'm afraid we need to check your sample file (.pbix) instead of .csv file. Thanks.

         

         

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Common VLOOKUP Problems


    In this article we will address some common problems encountered with the VLOOKUP function such as:

    Number entered as text
    Inserting columns
    Wrong use of TRUE or FALSE for range_lookup argument
    Lookup_value not in the first column
    Copying formula without absolute reference


    In using VLOOKUP, we only need to remember the four needed parameters :

     

    WHAT, WHERE, Column Number, Closest Match

    lookup_value – the WHAT parameter, this is what we want to look for
    table_array – the WHERE parameter, this is where we want to look, where lookup_value can be found in the leftmost column
    col_index_num – the COLUMN NUMBER, this is the column number of the data we want to extract, starting the count from the leftmost column of table_array
    [range_lookup] – the CLOSEST MATCH; if TRUE, we want to find the closest or exact match, if FALSE, we only want to find the exact match

     

     

    Regards,

    RacheL Gomez