Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hemingt
Helper V
Helper V

How to create several filters which should applyed several tables?

Hi Team,

 

I have several tables which contain 3 same columns named as 'Project', 'Version', 'CyleName',

I want to create three level filters for 'Project', 'Version' and 'CyleName'. and this filters should apply all the tables.

 

I have some lists on the same page for these tables, what I want is, if I select the Project to A, all the lists should only display the datas of Project A. If I select the Project to A and select Version to Version B, all the lists should only show the datas of (Project = A and Version = B ).

 

Do you share your good idea? thank you very much!

 

7 REPLIES 7
Anonymous
Not applicable

Hi hemingt,

 

For this, you should be apply joins by joining these 3 tables with the relationship key.

 

Like,

         SELECT * FROM Table1 T1

         INNER JOIN Table2 T2 ON T1.Key = T2.Key

         INNER JOIN Table3 T3 ON T2.Key = T3.Key

 

Second, You have to create hierarchy from the above created dataset.

Hierarchy should be like Project -> Version -> CycleName

 

You can create hierarchy by right clicking on the Columns in power BI and select create hierarchy.

Or, you can simply drag and columns one by one like Project column, then followed by version and then CycleName to the axis.

 

This should provides you the solution for your requirement.

 

Regards,

Pradeep

@Anonymous thank you for your reply.

 

in actual, there is no key relation between these tables.

I shared an excel for example.

 

https://1drv.ms/x/s!AsM9bO8zGUN6qnGmCaFm-w7WgYYL

 

I updated the shared excel, and add one column map_key which can idenfy the Porject+Version+CyleName.

It maybe helpful to create the filter?

 

I saw the below code in the post. I did not understand well but I think it may also can use in my case.

Could you help to give some advice?

 

https://community.powerbi.com/t5/Desktop/how-to-add-date-range-filter/m-p/423598#M194820

 

Expand date table =
VAR _calendar =
    CALENDAR ( MIN ( Table[start] ), MAX ( Table[end] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Table, _calendar ),
            Table[start] <= [Date]
                && Table[end] >= [Date]
        ),
        "id", Table[unique ID],
        "Date", [Date]
    )

 

Anonymous
Not applicable

The provided shared onedrive excel files are not responding. It throws an error that the site cannot be reached.

 

Regards,

Pradeep

it's strange. I can open it normally.

 

Anyway, I shared another excel, please get from https://1drv.ms/x/s!AsM9bO8zGUN6qnNJpyx1qML6j6wA

Hell @Anonymous  is there any feedback? thank you!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.