How to - Make and use a short custom list to filter a huge table

[drewP76]

Hello,

I'm pretty green with PowerBI so help and details is appreciated.

I have a very large table of applications (Publisher, Product, Name, Version, etc).  This list is dynamic and will grow/shrink as time passes.  This table links to computers, and users.

I want to create a static list of my most important applications (for example, applications we have to pay for).  And I can use that information to use as a filter for my main applications table.

For example:

"MyImportantApps"

- Microsoft Visio

"AllApplications" \ Product Name

- Adobe Reader

- Microsoft Visio 2010

- Microsoft Visio 2013

- and many many more

Static list (or table) "MyImportantApps" will filter "AllApplications" and show both of the Microsoft Visio products.  Note that I'm hoping that the static list can be a portion of Product Name values (IE: contains " *Microsoft Visio* ").

Can you provide any tips on how to accomplish this?  I can create a seperate static table (or list), but I dont know how to filter the other table.  I'm open to other methods if there is a preferred way to do this.

In case it helps, the source is our MECM (aka SCCM) database.  And I will be tying this info to other AD and MSOL reports I've created so far.

Thanks gurus!

[Anonymous · Accepted solution]

Hi  @drewP76 ,

You can use [Microsoft Visio 2010], [Microsoft Visio 2013] to Unpivot Columns to form a new table. When there is a relationship between the two tables, you can filter directly. When there is no relationship between the two tables, you can create a measure by creating a new table to set is =1 to filter

I created some data:

TableA:

TableB：

Custom rules:

Filter [Microsoft Visio 2010], [Microsoft Visio 2013] data from 2021.1.1 to 2021.1.3 in Table 2 by selection A in Table 1

Here are the steps you can follow：

1. You can in power query - select [Microsoft Visio 2010], [Microsoft Visio 2013] two columns - click Unpivot Columns.

Result:

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table'[Group])
return
IF(
_select="A"&&MAX('Table2'[Date])>=DATE(2021,1,1)&&MAX('Table2'[Date])<=DATE(2021,1,3),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[lbendlin]

You can maintain your static list directly in Power Query (the ETL part of Power BI).  This may seem convenient but it also limits your flexibility in case that "static" list isn't static. 

What we normally do is use the BMT (business managed table) concept.  This can be an Excel file on a sharepoint, or even a sharepoint list.  You connect to that data source from Power BI and add it to your data model so that it can impact the other tables when you filter.

[drewP76]

Thanks for the list about the external list Ibendlin, will figure that one out next.  Before I do, I'd like to know if & how that list can be used to filter another table that I mentioned previously.