I have two unrelated tables. I want to select values from one table, and then sum related values in that table. I then want to filter the second unrelated table using the sum, based on a formula comparison with a column in the second table.
Table 1:
| Property | Value |
| Red | 1 |
| Orange | 2 |
| Yellow | 3 |
| Green | 4 |
| Blue | 5 |
| Indigo | 6 |
| Violet | 7 |
Table 2:
| Name | Value |
| Fred | 24 |
| Barney | 26 |
| Wilma | 42 |
| Betty | 17 |
So I want to be able to have a slicer of the Property values from Table 1. If I select Red and Blue, the sum of the related value is 6. I then want to filter Table 2 to show all names where the associated Value in Table 2 is a multiple of the sum of values in Table 1. In other words, I want to see Fred and Wilma, as 24 and 42 are multiples of 6 (the sum of 1 and 5 for Red and Blue).
I have the two parts working separately. I have created a measure to calculate the sum of selected values in Table 1's slicer. And I have created a calculated table to output a subset of Table 2, based on multiples. But I've only been able to do it by using hard-coded values in the calculated table. When I try to substitute the hard-coded value with the measure from Table 1, I get Table 2 returned entirely.
I'm not sure that calculated tables (via DAX) are the way to go. I've also tried adding a calculated column to Table 2. I'm not even sure if DAX is the appropriate way to solve this, which is why I've posted in this forum instead of DAX.
