I need help to display sorted accounts in a table that has unique values

[Kev_G]

Hi, I would like to ask for help in Power Bi to evaluate a column. Please see below sample

Account	Deployment
Company1	WFH
Company1	Office
Company2	WFH
Company3	Office
Company 4	WFH
Company 5	Office

I'd like to create a table that I can use for the Slicer Visual:

1. Showing the accounts that have WFH Deployment only

2. Showing the accounts that have Office Deployment only

3. Showing the accounts that have both Office and WFH deployment

Thank you!

[v-yalanwu-msft · Accepted solution]

Hi, @Kev_G ;

You could create a flag measure.

1.create a table as slicer.

slicer = VALUES('Table'[Deployment])

2.create a flag measure.

flag = 
var _contain=SUMMARIZE(FILTER(ALL('Table'),[Deployment] in ALLSELECTED('slicer'[Deployment])),[Account])
var _not=SUMMARIZE(FILTER(ALL('Table'),NOT( [Deployment]) in ALLSELECTED('slicer'[Deployment])),[Account])
return IF( ISFILTERED(slicer[Deployment]),
         IF(
           DISTINCTCOUNT(slicer[Deployment])=CALCULATE(DISTINCTCOUNT(slicer[Deployment]),ALL(slicer)),
            IF(CALCULATE(DISTINCTCOUNT('Table'[Deployment]),ALLEXCEPT('Table','Table'[Account]))=DISTINCTCOUNT(slicer[Deployment]),1,0), 
             IF(MAX('Table'[Account]) in EXCEPT( _contain ,_not),1,0)),1)

3.apply it into filter.

The final output is shown below:

   

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Kev_G 

Easier to start with adding a new calculated column to you existing table (Accounts Table). 

Number Of Deployments=

COUNTROWS (
FILTER ( Accounts, Accounts[Account] = EARLIER ( Accounts[Account] ) )
)

Then create your tables 

Table 1 =
FILTER (
Accounts,
Accounts[Deployment] = "WFH"
&& Accounts[Number Of Deployments] = 1
)

Table 2 =
FILTER (
Accounts,
Accounts[Deployment] = "Office"
&& Accounts[Number Of Deployments] = 1
)

Table 3 =
FILTER (
Accounts,
Accounts[Number Of Deployments] = 2
)