Forum Discussion

amragl's avatar
amragl
Frequent Visitor
3 years ago

Matrix comparing 2 profiles selected from 2 slicers considering other slicers selection.

I created below Model which implies the indicated Hierarchy (non-created explicitly) to obtain Matrix result listed below.

 

All the Group A aggregations are correct, however for Group B, only Cat1 values are aggregated as shown on Faulty Matrix Below instead of getting the desired Matrix.

 

There are 5 slicers affecting Matrix calculations:

  • Profile A affecting is own table
  • Profile B affecting is own table
  • Cat1 affecting both tables GroupA and GroupB.
  • Cat2 affecting both tables GroupA and GroupB.
  • Cat3 affecting both tables GroupA and GroupB.

For some reason the for Cat2 and Cat3 it creates a blank colapsable row showing values for these, but not in the Cat1 with name.

 

**Update1**

Trying to add a PowerBI with a simplified version, siulating the required. (for some reason Profile 2 incut is not shown in matrix, but should be). I'll try to find time to amend the Profile2 incut issue, and how to upload the file differently.

 

Any advise will be hugely appreciated. Many thanks.

 

 

Can someone help me resolve this puzzle, please? I'been stuck on this for weeks now and I am doubting is even possible.

 

Many thanks.

 

 

 

Model:

---------------------------------------------------------------

 

 

***Attempted solution not displayed in this question:

I tried doing a star Model, with a disasociated Profile table and doing the calculations by aggregating by selected Profile B table, but the slicers behaved very differently bettwen Profile A and Profile B due to the context filters. I found impossible to create using this model. I also tried creating another disasociated Group table, but still no luck. 

 

Group_B_var1_total=

var _value = CALCULATE(SUM(Group[val1]), FILTER((ALL(Group[Prof(index])),Group[Prof(index] = SELECTEDVALUE(ProfB[index])))

var _result = IF(ISBLANK(_value), 0, _value)

return

_result

*********************************

 

 

2 Replies

  • amragl if you can put everything in a simple pbix file with the expected output it will be easier to understand the problem to provide the solution.

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

    If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️