Joins don't work when measures are in visualization

[gspollock]

Hi all,

I have a sales fact table joined with another table that is used to "Act As" an end user.  The end users are in a hierarchy going from Account Executive -> Sales Manager -> Vice President. 

A slicer references a column in the Act As table that corresponds to the Account Executive or Sales Manager or Vice President name.  When that user is selected in the slicer, it should only show the records that belong to that Account Executive or Sales Manager or Vice President.  When no measures are included in the visualization the slicer works as it should.  However when a measure is included, it shows all VP's etc., with the measures blank except for the selected VP.  This problem could be corrected by setting a "not blank" filter on one of the measures.  However there are some people who could be blank that we want to show, such as Account Executives under the selected VP.

I think this will be a problem with row level security, where for example a VP would see the names of all the other VP's as well as their Sales Managers and Account Executives.  Our RLS works by joining on a table, although not the same table as the Act As.

Is this behavior expected or is something not set up right?  Is there a way around it?

Thanks

After some tinkering I found that only certain measures cause the behavior.  I changed one measure and it no longer causes it:

This causes the behavior:

Number Accounts Shared Quarterly = var numshare = CALCULATE(DISTINCTCOUNT(FACT_MBA_REPORT_SHARE[account_id]),filter('FACT_MBA_REPORT_SHARE',[COUNT_TOWARDS_MBA_MBO]="Yes")) return if(ISBLANK(numshare),0,numshare)

This does not:

Number Accounts Shared Quarterly = CALCULATE( COUNTROWS(VALUES (FACT_MBA_REPORT_SHARE[account_id])),filter('FACT_MBA_REPORT_SHARE',[COUNT_TOWARDS_MBA_MBO]="Yes"))

This measure and some others that are similar is causing it:

Meets Quarterly Share Goal = CALCULATE(if('FACT_MBA_REPORT_SHARE'[Total Met Goal]= 'FACT_MBA_REPORT_SHARE'[Total Account Managers]&&'FACT_MBA_REPORT_SHARE'[Number Accounts Shared Quarterly]>0,"Yes","No"))

This is Total Met Goal:

Total Met Goal =

SUMX (VALUES ( FACT_MBA_REPORT_SHARE[eID] ),

INT ( [Number Accounts Shared Quarterly] > 3 ))

Perhaps there's a way to modify either or both of those measures?

[v-yueyunzh-msft]

Hi, @gspollock 

According to your description, it works fine when you use your slicer. But when you add a metric value, it will show some blank values that you don't want to show.
This may have something to do with your metric value return. When your metric value does not return BLANK, the row will be displayed in the table, even if you use a slicer to filter. If you don't want to display certain rows, you really need to modify your measure to return the corresponding value for different situations.

You can try to modify the problematic metric to check if the problem is solved.
And it is very difficult to analyze the cause of the problem by simply relying on DAX statements. If you still can't solve the problem, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[gspollock]

I have a pbix you can look at.  How can I upload it to your OneDrive?

Thanks!

[v-yueyunzh-msft]

Hi , @gspollock 

You can try to delete your sensitive data, keep some test data, and describe your expected output and calculation logic. You can upload the excel or pbix file to your own OneDrive, and then share it with me by link.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[gspollock]

This is the pbix https://tinyurl.com/pbix-file

I'm not sure if you would be able to view the file since it's in our network.

If you look at the first visual, when a value is selected from one of the "Act As" slicers, the ones that are not selected are still there with blank values for the measures.  The "Meets Quarterly Share Goal" measure is causing it.  If you remove that measure from the visual then the others disappear as they should.  Filtering "Number Accounts Shared Quarterly" to be not blank would solve that but there are some records that have blank values at the lowest level that would disappear.  

There are three fields in the Act As slicers - VP_E_ID, SLS_MGR_E_ID and ACCT_MGR_E_ID - from three tables: DIM_OBIEE_PROXY_USER_RVP, DIM_OBIEE_PROXY_USER_ASM and DIM_OBIEE_PROXY_USER_AM.  Those tables join to the Fact table through the DIM_OBI_PC_CUST_SLS_HIER table.  This method has been used in many reports.  There seems to be something about the measure.

Thanks for your help!

[v-yueyunzh-msft]

Hi, @gspollock 

Thanks for your quick response!

Sorry, I don't have permission to access your pbix file, you can try to provide me with some similar test data and the relationship between tables, and then provide me with the end result you want to output in the form of a table?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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