Calculate target based on the origin value (in the filter)

[Anonymous]

Hi, 

I have troubles to create the DAX measure for the context below : 

The model : 

2 dimensions Origin (key O) and Target (key T) which have the same values.

The fact table contains 2 columns Origin (O) and Target (T).

Context : 

A filter on the dimension Origin

For each / a set of Origin value(s), I have to count the number of the the same values in Target. 

By summary, I need to calculate the number of Target which have the same values to chosen Origin ones. 

Concret example : 

Dimension O contains : v1, v2, v3

Dimension T contains : v1, v2, v3 (same values to dimension O)

Fact table contains : 

O   T

v1  v2

v2  v3

v3  v1

v1  v1

v2  v3

v3  v1

I have to produce a table with :

O   Measure

v1  3 (as v1 appears 3 times in T column in fact table)

v2  1 (as v2 appears 1 times in T column in fact table)

v3  2 (as v3 appears 2 times in T column in fact table)

Many thanks for your help,

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a measure as follow:

Measure = CALCULATE(COUNT('Fact'[T]),FILTER('Fact',[T]=MAX('dimension'[O])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.