Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Best DAX filter to lookup a value for categories

I currently have a POWERBI report I want to create that will allow us to be able to associate users to categories to determine what access to activity codes they have the tables I ahve are below but only two of them have a relationship.  Two of the others are the activity codes and they dont have any associate to each other nor do the two activity code fields have an association to each other.


Collector table has a relationship with with Securty set table, but the way the security sets are labeled, they are using the Y,N for each security category. (Categlory 1-9) Uses the Y,N as seen below.

Collector table maps to the security Set Column through relationship 1 to many.

Security Set and Collector Table below:




Collector Table:






Activites uses the security categories 1-9 to map to the security set which is a table of roles that users are inwhich shows users access to activty codes under that corresponding security category. So if they are in act_sec_cat1 they have access to the activity codes in that category.



Activity Table:




APF Table:



Activity tables below have the same security category layout to map to the security set.

APF table however, uses the same 1-9 security category but its in a different labeled column under security_Level.

Basically these two tables are just tables of activity codes the problems is getting them associate with the security sets so I can see collectors who have access to a particular APF and activity code so I can manage it via this report. Is there a way to break these down in the security sets to create some type of relationship to the activities and APF tables, maybe using a way to break down the act_sec_cat or make this easier. Any assistance would surely be appreciated!

Super User
Super User

@cewjr9842 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.


You can create a common security category table and join with both tables, if that can help


Bridge Table:

On here how would I provide you with the test samples tables? I am using a excel spreadsheet and have cleaned up the data but not sure how to attach it. Also I was able to bridge the Activity table and the APF table but the other issue with Security Set and Collector Table is still my biggest issue. I tried to wirte a SUMX dax statement but cant get the correlation with the security sets to work.



Community Support
Community Support

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors