How to Fetch Common records from The Slicer selection in Power BI

[Anonymous]

I have one dataset which contains the report and mete data information. Based on report name selection user would like to see what are all the common tables and columns used in those reports, Below is the Metadata and expected Outputs. Is there any solution for this. Appreciate your help.
Thanks,

[Anonymous]

@Anonymous  Firstly, thank you so much for your  response. Yes it's working as expected.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here's my solution.

1.Separate the main table into three tables, one with Report column and Table column, another with Report column and Column column, and the third one as long as the Report column. Created by calculated tables.

Table = DISTINCT(SELECTCOLUMNS('Main',"Report",[Report],"Table",[Table]))

Column = DISTINCT(SELECTCOLUMNS('Main',"Report",[Report],"Column",[Column]))

Report = DISTINCT(Main[Report])

Relationships:

2.Create two measure as the visual-level filters

IfTable = var _count=CALCULATE(COUNT('Table'[Table]),FILTER(ALLSELECTED('Table'),[Table]=MAX('Table'[Table])))
return IF(COUNT(Report[Report])=_count,1)

IfColumn = var _count=CALCULATE(COUNT('Column'[Column]),FILTER(ALLSELECTED('Column'),[Column]=MAX('Column'[Column])))
return IF(COUNT(Report[Report])=_count,1)

3.Create two visuals as follows, set show items when the value is 1.

Results:

Best Regards,

Stephen Tao

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