Force indirect relationship in dax?

[Anonymous]

I have the below model:

And when I want to display revenue by segment, it just gives me the totals:

(The above is wrong; it should roughly be half for A and half for B, but it just displays totals 3 times...)

Is there a function in dax I have to use to enforce this segmentation?? (the segmentation comes from ClientType, which is not directly related, but through Client.

[Anonymous]

thanks for your answer, unfortunately I get:

[Greg_Deckler · Accepted solution]

@Anonymous You could try:

CALCULATE(
  SUM('Revenue'[Revenue]),
  TREATAS(DISTINCT('ClientType'[ClientNo]),'Client'[ClientNo])
)

How many records are in each table? Also, the relationships shown in your image seem to be impossible. You have multiple paths between the Client table and the Revenue table and so there is a relationship that would need to be inactive.

[Greg_Deckler]

@Anonymous Well, you could use a technique I call DAX Table Hopping:

Measure = 
  VAR __Clients = DISTINCT('ClientType'[ClientNo])
  VAR __KeyCustomers = DISTINCT(SELECTCOLUMNS(FILTER('Client',[ClientNo] IN __Clients),"__Key_Customer",[Key_Customer]))
RETURN
  SUMX(FILTER('Revenue',[Key_Customer] IN __KeyCustomers),[Revenue])