Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

OPTIMIZE DAX MEASURE

Hello!

 

I need help! 

 

I use this measure for row level security. 

 

In my dashboard, users can see some flows and some states of flows. I define this rules in FLOWS_HO_USERS_ACCESS:

EX:

USERDIM_FLOW_STATUS_ID
A100
A101
B200
B205
B210

 

and flows in FCT_FLOWS: 

FLOWDIM_FLOW_STATUS_ID
A100
A101
B200
B205
B210

 

and one dimension for the flow states:

DIM_FLOWS_STATUS:

DIM_FLOW_STATUS_IDNAME_STATUS
100A-1
101A-2
200B-1
205B-2
210B-3

 

I used this measure, but it works very slow:

 

MyMeasure =
FORMAT ( FCT_FLOWS[DIM_FLOW_STATUS_ID], "General Number" )
= FORMAT (
IF (
ISBLANK (
LOOKUPVALUE (
FLOWS_HO_USERS_ACCESS[DIM_FLOW_STATUS_ID],
FLOWS_HO_USERS_ACCESS[USER], USERNAME (),
FLOWS_HO_USERS_ACCESS[DIM_FLOW_STATUS_ID], 'FCT_FLOWS'[DIM_FLOW_STATUS_ID]
)
),
IF (
LOOKUPVALUE (
FLOWS_HO_USERS_ACCESS[USER],
FLOWS_HO_USERS_ACCESS[USER], USERNAME ()
)
= BLANK (),
FCT_FLOWS[DIM_FLOW_STATUS_ID],
-1
),
LOOKUPVALUE (
FLOWS_HO_USERS_ACCESS[DIM_FLOW_STATUS_ID],
FLOWS_HO_USERS_ACCESS[USER], USERNAME (),
FLOWS_HO_USERS_ACCESS[DIM_FLOW_STATUS_ID], 'FCT_FLOWS'[DIM_FLOW_STATUS_ID]
)
),
"General Number"
)

 

How can I optimize this measure?

 

 

Thank you!

 

Need Help

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    Where did you use this measure? It seems this measure is not used in the Table filter DAX expression box for RLS. 

     

    For Row level security, the DAX expression entered in the Table filter DAX expression box should return a value of true or false. For example: [Entity ID] = “Value”. This is used to filter rows according to the true or false result. According to your table, you can try a DAX expression like [User] = username() . 

     

    In addition, you can create relationships between tables. Relationships can pass filters between tables. This is more efficient than using LOOKUPVALUE function. 

     

    Here are some RLS tutorials for your reference. Note that if you want to use username() or userprincipalname(), this is dynamic RLS.

    How to Setup Static and Dynamic Row Level Security (RLS) in Power BI? 

    Implement row-level security 

     

    Hope this helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello, v-jingzhang !

       

      I use this measure in SSAS, in my tabular model. 

      I don't have active this option in my PowerBi.

      I need to implement rls for:

      1. Dynamic filtering. When a user from one division enters, the information associated with the division should be filtered automatically.
      2. Static filtering. When a management user enters, he can view all the divisions and filter the information in the interface.

       

       

      For first i use measure in Rols -> SSAS.

      For second i use relationships:

       

      In this moment, works. But not very fast when i apply dynamic RLS.