Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
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
Solved! Go to Solution.
Hi @v-xiaotang , sorry for file expired, follows the new link again.
I can't share the pbi, because sensitive data.
I think my problem is related with this link -> Parent Child Hierarchies
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
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.
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)
Thanks fo help me
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.
I can't share the pbi, because sensitive data.
I think my problem is related with this link -> Parent Child Hierarchies
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |