Thanks for the reply from@vigneshba and@lbendlin,please allow me to provide another insight:
Hi,@Jessica_17
Regarding the issue you raised, my solution is as follows:
1.Next create relationships between tables:
2.The visualization object is then modified to the following result:
3.Here's my final result, which I hope meets your requirements.
This is the more relevant approach to your needs as far as I'm concerned.
4.Of course, we still recommend using the following three options if allowed:
First, use the merge query in powerquery:
Merge queries overview - Power Query | Microsoft Learn
Second, using custom columns in powerquery:
Add a custom column in Power BI Desktop - Power BI | Microsoft Learn
Third using the lookupvalues() function in dax:
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
RELATED function (DAX) - DAX | Microsoft Learn
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @v-linyulu-msft ,
Thanks for providing this solution, I think I was not able to deliver in my questions was that according to your data also, that Need to so same in Matrix table, but when I convert your data also into matrix table, it is showing values according to cross join, such as Cat "abc" do not have concat value as "kk-ll" but it is showing that value in front of "abc" Cat, even I checked the data , it does not have that data in front of Cat, so basically it should filter as what ever the associated values are in Concat column, (so only 2 values in Concat should have been shown in front of Cat "abc").
Hi,@Jessica_17
Thank you for your reply.
First of all, for your needs, I recommend using table visualizations for more convenience and intuitiveness, and matrices for aggregate hierarchical values.
Second, if you still need to display that value in the matrix, here's my solution:
1.First, you need to insert the serial number in PowerQuery to ensure that the values are not aggregated:
2.Next, copy the table and name it "head":
3.Then unpviot it:
4.Finally, get rid of duplicates and unnecessary rows:
5. Below are the measure I've created for your needs：
Measure =
SWITCH(TRUE(),
MAX('head'[Attribute])="Cat",CALCULATE(MAX('Table'[Cat]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="Act",CALCULATE(MAX('Table'[Act]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="concat",CALCULATE(MAX('Table'[concat]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="cust",CALCULATE(MAX('Table'[cust]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="metric",CALCULATE(MAX('Table'[metric]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="Sub Cat",CALCULATE(MAX('Table'[Sub Cat]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
MAX('head'[Attribute])="trad",CALCULATE(MAX('Table'[trad]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])))
,FALSE())6. Then modify the visualization settings:
7.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-linyulu-msft
Thanks for the solution, but I think I made it a little complex, I do require matrix only and only the concat part needs to be filtered according to previous column values as you had done in previous steps, rest are solvable as you have already completed those part.
Helpful resources
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
