Table with Records similar to current Selection

[Sblue]

I have a table with multiple columns and records. I am using slicers to filter this table for my report.

What I am having problems with is the following:

I am trying to generate a table (to be displayed as a visual) with records similar (based on a common field value) to current filtered records.

Example:

TABLE Account

ID    AccountNumber   AccountName   AccountType

1          AN192345           Aaa                    Type1

2          AN199833           Baa                    Type2         

3          AN192167           Caa                    Type1

4          AN192341           Aca                    Type1

5          AN192342           Dda                    Type2

6          AN192343           Bdd                    Type3

7          AN192344           Eee                     Type1

Slicers:

AccountNumber = AN192345 

AccountType = Type1

I want to know which accounts share the same first 6 characters of the selected account ("AN1923")

Something like: "Select rows from TableA where left(Accountnumber,6)  =  left(SlicerSelection(Accountnumber),6)"

Expected Output:

ID    AccountNumber   AccountName   AccountType

1          AN192345           Aaa                    Type1     

4          AN192341           Aca                    Type1

5          AN192342           Dda                    Type2

6          AN192343           Bdd                    Type3

7          AN192344           Eee                     Type1

Any help is much appreciated.

[v-cherch-msft · Accepted solution]

Hi @Sblue

First, you may create a new table as Slicer Table:

Slicer =
DISTINCT ( 'TABLE Account'[AccountNumber] ) 

Second, you may try to create measure as below:

Measure =
IF (
    HASONEVALUE ( Slicer[AccountNumber] ),
    IF (
        LEFT ( VALUES ( 'TABLE Account'[AccountNumber] ), 6 )
            = LEFT ( SELECTEDVALUE ( Slicer[AccountNumber] ), 6 ),
        1
    )
)

Regards,

Cherie

[Sblue]

Thanks Cherie, this worked!