Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RafaelRSantos_
Frequent Visitor

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 resultPath 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

 

With filter not blank applied.pngWithout filter not blank applied.png

 

Thank for help

1 ACCEPTED SOLUTION

Hi @v-xiaotang , sorry for file expired, follows the new link again.

Arquivo 

I can't share the pbi, because sensitive data.

I think my problem is related with this link -> Parent Child Hierarchies 

View solution in original post

5 REPLIES 5
RachelGomez1619
Helper II
Helper II

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

v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1658418808042.png

 

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.

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

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.

Hi @v-xiaotang , sorry for file expired, follows the new link again.

Arquivo 

I can't share the pbi, because sensitive data.

I think my problem is related with this link -> Parent Child Hierarchies 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.