Forum Discussion
Slicing two tables
Hello, I’m relatively new to DAX and struggling with the following, so any help would be greatly appreciated. Apologies, if this has been asnwered already, but I couldn't find anything similar.
I’ve the following two tables.
TABLE1
| Combination | Index |
| abcd | x |
| adc | y |
| cbd | y |
| bd | y |
| ac | x |
TABLE2
| ID | Count |
| a | 3 |
| b | 3 |
| c | 4 |
| d | 4 |
Table2[Count] describes how often Table2[ID] appears in Table1[Combinations], specifically:
Count = CALCULATE(COUNTA(Table1[Combination]),FILTER(Table1, SEARCH(Table2[ID], Table1[Combination],,0)))
These table are linked through Table1[Combination] and Table2[Count].
I would like to filter through Table1[Index] and have Table2 automatically updated but so far I haven't managed to. For example, when selecting Tabl1[Index] = x, I would like:
| ID | Count |
| a | 2 |
| b | 1 |
| c | 1 |
| d | 1 |
What am I missing? Thank you very much in advance!
Hi Anonymous ,
You could try SELECTEDVALUE() function.
Count= CALCULATE ( COUNTA ( 'Table1'[Combination] ), FILTER ( 'Table1', SEARCH ( SELECTEDVALUE ( 'Table2'[ID] ), 'Table1'[Combination],, 0 ) ) )But I think the count of c should be 2 when you select x.
6 Replies
Well, if these are actually tables then what you are trying to do cannot be done because tables only update upon data load/refresh.
You would need a measure.
- Generally you just wrap it with MAX or MIN depending on your preference. Doesn't matter what the aggregation is when it is filtered to one. Anonymous
