Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX formula for Client Matrix - Recognizing Business Units

I'm looking to create a Client Matrix for our organization. The user would either select a Client (or several clients) OR our Business Unit(s), in order to see where there are opportunities (or conve...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

I have built a data sample like this:

And according to my understanding, you want to show all related( as Who's In )/not related ( as Who's Out) Business Unit for all selected Client in slicer ,right?

 

Please follow these steps:

1. Create a new table for slicer:

ForSlicer = VALUES('Table'[Client])

2. Create a flag measure for the selected (as 1) /not selected (as 0) value:

Flag = IF(MAX('Table'[Client]) in ALLSELECTED(ForSlicer[Client]),1,0)

3.Now use the following formulas:

Who's In = 
var _t=SUMMARIZE(FILTER('Table',[Flag]=1),[Business Unit ])
return CONCATENATEX(_t,[Business Unit ],",")
Who's Out = 
var _t=SUMMARIZE(FILTER('Table', [Flag]=0),[Business Unit ])
return CONCATENATEX(_t,[Business Unit ],",")

The final output is shown below:

Or if you just combine all related Business Unit into one row and show it in matrix, please try this:

In Matrix = CONCATENATEX(VALUES('Table'[Business Unit ]),[Business Unit ],",")

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Eyelyn,

Thank you for this information. It's definitely taking me in the correct direction. It looks like the Who's In element is working correctly. However, the Who's Out element is not: nothing is populating:

Here the formula I have in for each:

 

Who's In =
var _t=SUMMARIZE(FILTER('Opportunity Splits',[Flag]=1),[BU__c])
return CONCATENATEX(_t,[BU__c],",")
 
Who's Out =
var _t=SUMMARIZE(FILTER('Opportunity Splits',[Flag]=0),[BU__c])
return CONCATENATEX(_t,[BU__c],",")
 
Flag = IF(MAX('ForSlicer'[Client]) in ALLSELECTED(ForSlicer[Client]),1,0)
 
here are the tables:
 
Also, do you know how I can make it so that the Who's In / Who's Out returns appear in alphabetical order? The current return is rather confusing to review.
 
Thank you!
Thomas