DAX formula for Client Matrix - Recognizing Business Units

Question

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 conversely where there is overlap).

In the "Who's In?" box, it would list (comma separated) all the agencies that HAVE business with the selected clients.
In the "Who's Out?" box, it would listed (comma separated) all the agencies that DO NOT have business with the selected clients.

One issue, as you can see, is that as the user makes their selection, it populates the grid/matrix with the Business Unit(s) that apply to that criteria (for example, by Account/Client). However, it does not leave a row for ALL the agencies (meaning, if a Business Unit does not have business with the Account/Client, they are nowhere to be seen: however, that's precisely why it should be listed in the "Who's Out"? flashcard.)

I believe I need to generate a formula that will recognize ALL of the Business Unit(s), so that I can then recognize "Who's In?" and "Who's Out?"

Any help is appreciated.

Thank you.

Thomas

ashish_mathur · Answer

Hi,
Share the link from where i can download your PBI file and show the expected result for a certain client selection.

anonymous · Answer

Hi Ashish,Do you have a recommendation for how/where to share the file? It's a massive file - in the past I've had issues with uploading to the PowerBI support system.&nbsp;Thank you.

ashish_mathur · Answer

Do not share a massive file.&nbsp; Share a small representative file, explain the question and show the expected result.&nbsp; Upload the file to Google Drive and share the download link here.

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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?
&nbsp;
Please follow these steps:
1. Create a new table for slicer:
ForSlicer = VALUES('Table'[Client])

2. Create a flag measure&nbsp;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 QinIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

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:&nbsp;Who's In = var _t=SUMMARIZE(FILTER('Opportunity Splits',[Flag]=1),[BU__c])return CONCATENATEX(_t,[BU__c],",")&nbsp;Who's Out = var _t=SUMMARIZE(FILTER('Opportunity Splits',[Flag]=0),[BU__c])return CONCATENATEX(_t,[BU__c],",")&nbsp;Flag = IF(MAX('ForSlicer'[Client]) in ALLSELECTED(ForSlicer[Client]),1,0)&nbsp;here are the tables:&nbsp;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.&nbsp;Thank you!Thomas