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
cewjr9842
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:

 

cewjr9842_5-1644423031133.png

 

Collector Table:

 

cewjr9842_1-1644422909697.png

 

 

 

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:

cewjr9842_1-1644422077303.png

 

 

APF Table:

cewjr9842_2-1644422296773.png

 

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!

3 REPLIES 3
amitchandak
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: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

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.

 

 

Icey
Community Support
Community Support

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.

Top Solution Authors