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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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