Create a count measure which count rows from multiple tables in my organization data model

[haiderAli]

Hi, 
Can any body please help me with that? 

Query: I want to create a measure to show me count of applications id which I will display on card. Applications id are column in dim application table. We have another column security_scheme_id in applications table which is the security scheme for application. Another table which is not directly related to applications table but has connection through another tables in a model which has columns like summary and detail. If I use that column in my visual table then it filters out applicatoins id. So I want to count the number of applicatoins id based on each user credentials which is already in master security table. 

Approach1: We can create measure which gives me a join b/w two tables that is security and dim applications but it should be right join as we want all applications id associated with the user security. 

Approach 2: Count the visual table that takes fields from two tables but I am not sure how to convert visual table in my model? 

Limitation :I am using company data model so would'nt be able to add a table/column. 

[haiderAli · Accepted solution]

Hi all, thanks for your responses. I have find a way for solving this problem by creating measure. 

Count_Application = "Total number of Applications: " & CALCULATE(distinctcount('Dim Application'[Application_ID]),FILTER(Security,Security[IsSummaryAllowed]=1))

Thanks everyone for posting answers on my query.

[ValtteriN]

Hi,

You can use COUNTROWS and INTERSECT to achieve this. But since you have the option to filter the security group we only need a simple countrows. So something like this: COUNTROWS(VALUES(Table[ApplicationID]))

Example:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[haiderAli]

Hi Valtterin,

Thank you soo much for your response. Unfortunately its not solving my problem. I have tried it but its showing me count of applications id even when I am applying filter for another user which has access to only one application. FYR I have attach SC : ( Its showing 19 applications which are total number of applications present in application table. 

[v-yanjiang-msft]

Hi @haiderAli ,

According to your description, I create a sample, the formula works when Application ID is filtered.

I attach my sample below, if you can't post your sample data, could you please modify my sample data to show your problem?

Best Regards,
Community Support Team _ kalyj

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

[haiderAli]

Hi @v-yanjiang-msft 

Thanks for you response. 

I have modified your sample data based on my problem. I hope you will understand my issue which I am currently facing. I haven't filled the security table but modified the data model and add all the columns that we have. Moreover, I want to display no of applications based on user account (SAM_ACCOUNT_ID) which I have applied filter on all the pages. 

If a user has access to 16 applications, He should be able to see no of applications 16 not 19.

Please send me your email address and I will share the update version of sample as I don't have option to attach the pbix file on this post as I am a new user.