topic Best DAX filter to lookup a value for categories in DAX Commands and Tips

Best DAX filter to lookup a value for categories

cewjr9842 — Wed, 09 Feb 2022 16:25:35 GMT

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!

Re: Best DAX filter to lookup a value for categories

amitchandak — Thu, 10 Feb 2022 05:35:22 GMT

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

Re: Best DAX filter to lookup a value for categories

cewjr9842 — Fri, 11 Feb 2022 02:28:47 GMT

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.

Re: Best DAX filter to lookup a value for categories

Icey — Mon, 21 Feb 2022 09:33:41 GMT

Hi cewjr9842 ,

You can refer to these blogs:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey