topic Re: Create a count measure which count rows from multiple tables in my organization data model in DAX Commands and Tips

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

haiderAli — Fri, 04 Feb 2022 05:41:16 GMT

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.

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

ValtteriN — Fri, 04 Feb 2022 06:35:08 GMT

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/

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

haiderAli — Fri, 04 Feb 2022 14:27:02 GMT

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.

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

v-yanjiang-msft — Thu, 10 Feb 2022 09:18:59 GMT

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.

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

haiderAli — Fri, 11 Feb 2022 02:57:40 GMT

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.

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

haiderAli — Mon, 21 Feb 2022 02:02:02 GMT

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.