Filter a table by a slicer lookup to another table

[DataSc86]

I have a table that contains a lookup for clients and the type of client: 

Client	Type
Amazon	Ecom
Google	Search Engine
Walmart	Ecom
Best Buy	Ecom

I have a slicer for the client and it filters the whole report correctly, but I wanted to do a comparative analysis based on type on a comparison tab. So all the tabs should sync to "Amazon" when they click that (currently works fine without concern) but trying to build out a single tab and table that when you click on "Amazon" it shows the data for just the "Ecom" types ['Amazon', 'Walmart','Best Buy'] so I can show the market analysis. 

I have tried to play with multiple formulas and guides online but not able to do it yet based on any tutorial I have found. 

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBi file.

Hope this helps.

[Kedar_Pande]

@DataSc86 

Create a measure that filters the data for the selected client's type and retrieves data for all clients within that type:

ComparativeData =
VAR SelectedClientType =
SELECTEDVALUE(ClientLookup[Type])
RETURN
CALCULATE(
[YourMeasure], 
TREATAS(
VALUES(ClientLookup[Client]),
'FactTable'[Client]
),
FILTER(
ALL(ClientLookup),
ClientLookup[Type] = SelectedClientType
)
)

This approach dynamically adjusts to show the comparative analysis based on the selected client's type.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[v-xinruzhu-msft]

Hi @DataSc86 

You can refer to the following solution.

Sample data is the same as you provided.

Create a new table, the table has no relationship with other tables.

Type = SUMMARIZE('Table',[Client])

Then create a measure.

MEASURE =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Client] IN VALUES ( 'Type'[Client] )
    )
VAR b =
    COUNTROWS ( FILTER ( 'Table', [Type] IN a ) )
RETURN
    IF ( ISFILTERED ( 'Type'[Client] ), IF ( b > 0, 1, 0 ), 1 )

Then create a table visual and put the measure to the visual filter.

Then create a slicer and put the client field of the type table to the slicer, then filter.

Output

Best Regards!

Yolo Zhu

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

[DataSc86]

I am trying to make this work but I have a lot of tables that tie together and its not just really the single table interaction. I'll keep playing with it and once I validate I can make it work will accept as the solution

[v-xinruzhu-msft]

Hi @DataSc86 

Did the solution i offered before help you solve the problem, if it helps, you can consider to acceppt it as a solution so that more user can refer to, or if you still have other problems you can offer some more infotmation that can provide more suggestion for you.

Best Regards!

Yolo Zhu