DAX formula for Client Matrix - Recognizing Business Units

[Tschwenn]

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

[v-eqin-msft]

Hi @Tschwenn ,

On my side, everything works fine:

So can you please share your pbix file after removing sensitive data to help us clarify your scenario?

Best Regards,
Eyelyn Qin

[Tschwenn]

Hi @v-eqin-msft 

Can you confirm you you're able to access the file I provided?

[Tschwenn]

Hi Eyelyn9,

Can you confirm you you're able to access the file I provided?

[v-eqin-msft]

Hi @Tschwenn ,

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.

[Tschwenn]

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

[Ashish_Mathur]

Hi,

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

[Tschwenn]

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.

Thank you.

[Ashish_Mathur]

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