Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I've been trying to find a solution with the below scenario. I hope someone can help.
I want to get the number of rows in Table1 filtered by the "Value" field in Table2. I've created a custom column in Table2 to input the values.
I use the code "Count of Value = CALCULATE(COUNTROWS(Table1),FILTER('Table1','Table1'[Value]=Table2[Value]))" and it gets what I want. However, if I do filters on Table1 on the report, the values do not change. How do I link the values based on the filters selected?
Thanks,
Ashley
Solved! Go to Solution.
Instead of count of rows as a calculated column, try creating a measure as follows
Count of Values = CALCULATE(COUNTROWS(Table3), FILTER(Table3, Table3[Value] = MAX(Table4[Value]) ))
This should give you the desired result
Regards,
Thejeswar
Hi @aiweller,
When you say "However, if I do filters on Table1 on the report, the values do not change" what do you mean?
From your post, I get that Table 1 is your source and Table 2 has a calculated column which is based on Table 1.
Now you are adding a filter on Table1 and your Table 1 is not getting filtered
Can you clarify a bit more on what is the filter you are talking about and what are the values?
It's better if you can give some sample data and your expected result
Regards,
Thejeswar
Hi @Thejeswar,
Thanks for the reply.. What I mean is if I filter let's say, Country I only select "US" or Department I only select "Finance", the values in the calculated column in Table2 does not get updated. I would like to have the Table2 column show the number of rows resulting from the filter.
For example, if I select County "US" in Table1, the number in "Count of Value" in Table2 should only show values "3A" as 1 and "1B" as 1. The rest is 0.
Hope this explains.
Ashley
Hi @aiweller
Do you have a relationship between your two tables ? If yes you need to change it so it become a bidirectionnal relation
Go to the relationtab, double blick on the link between the tables and change the direction
It should work
- Quentin
Hi @quentin_vigne,
Thanks for the suggestion. If I do a relationship the values that do not have matching records do not show. I would like to have values that do not have any matching records as "0".
For instance if I do relationship between the tables based on the "Value" column from both tables, the value for "1A" will not show. It will look like below. Unless there is another way to display unmatched records on Table2. 🙂
Thanks,
Ashley
I've done this and it looks like it's working
I've added Value and Count of Table2 on the table
If you want to see 0 instead of blank add "+ 0" at the end of your formula that count row
Hi @quentin_vigne,
From your example, you have selected "US" and the departments "Finance" and "HR" is displayed, but the "Count of Values" is still counting the total number of "2A" in Table1. The expected result for "2A" should be 1 instead of 2(it is showing 2 because it's including the value from "France").
Thanks,
Ashley
Instead of count of rows as a calculated column, try creating a measure as follows
Count of Values = CALCULATE(COUNTROWS(Table3), FILTER(Table3, Table3[Value] = MAX(Table4[Value]) ))
This should give you the desired result
Regards,
Thejeswar
Hi @Thejeswar,
Thanks so much. Works perfectly as what I wanted to achieve.
Just to ask, do you know why the total is not showing? In the result below, the second table I added (+0) to the end of the code to show the rows that have no matching records.
Regards,
Ashley
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |