Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
i am with tow data sources and i want to count the amount of times a name appears in each data source and then do a percentage for example
Data Source 1 John Doe = 250
Data Source 2 John Doe = 4
in this case 4 is 1.6% of 250
Solved! Go to Solution.
@Jayfemi,
Firstly, create the following measure in the first table.
Count1 = CALCULATE(COUNTA(Source1[name]),FILTER(Source1,Source1[name]="John Doe"))
Secondly, create the following measure in another table.
Count2 = CALCULATE(COUNTA(Source2[name]),FILTER(Source2,Source2[name]="John Doe"))
Then create percent measure in the first table using dax below.
percent = [Count2]/[Count1]
If the above steps don't help, please share sample data of the two tables.
Regards,
Lydia
@Jayfemi,
Firstly, create the following measure in the first table.
Count1 = CALCULATE(COUNTA(Source1[name]),FILTER(Source1,Source1[name]="John Doe"))
Secondly, create the following measure in another table.
Count2 = CALCULATE(COUNTA(Source2[name]),FILTER(Source2,Source2[name]="John Doe"))
Then create percent measure in the first table using dax below.
percent = [Count2]/[Count1]
If the above steps don't help, please share sample data of the two tables.
Regards,
Lydia
Create a bridge table between your two data sources that contains a single unique value from each of the two data sources. Use this single table to create a relationship to both of your data sources.
If you want to do it with a specified name, you can do countrows with a calculate.
It sounds like you might want to create measures similar to this:
[CountTable1] = Countrows(DataSource1[Name)
[CountTable2] = Countrows(DataSource2[Name)
[%InBoth] = Divide([CountTable1],[CountTable2]) >> please note that I don't know what the appropriate numerator/denominator is so please decide yourself, and if you want to only show names that occur in both data sources please try
If( [CountTable1] && [CountTable2], Divide(x,y))
User | Count |
---|---|
90 | |
88 | |
87 | |
79 | |
49 |
User | Count |
---|---|
151 | |
143 | |
111 | |
74 | |
55 |