Help With Many to Many Relationship With Two Bridge Tables

[matthew_hampton]

Hello - 

I assume this is an easy question to answer but I have been starting at it for about an hour and cannot come up with the best want to handle it.

Here is my data structure:

What I am looking to do is:

Create a measure where I sum [FactScorecard]TransactionQuantity if [FactScorecard]AccountNumber = [FactChurnPropensity]AccountNumber and [FactScorecard]ChangeDate is between [FactChurnPropensity]ScoringDate and [FactChurnPropensity]EndDate

I want the visual with this measure to be a combo line/bar chart where the X-axis is [FactChurnPropensity]RiskLevel, the Y-Axis for the bar is a Count([FactChurnPropensity]AccountNumber) and the Y-Axis for the line chart should be the measure referenced above.

I want to be able to filter ScoringDate.

I was able to get the date filtering to work with SELECTEDVALUE but cannot nail down how to do the AccountNumber part.

Please let me know if you need more detail.

Matt

[v-yuta-msft]

@matthew_hampton ,

So the two fact tables are many to mant relationship, right? Try using dax like pattern CALCULATE(Your Measure, FILTER(Fact1, Fact1[Colum1] IN VALUES(Fact2[Column2]))).

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matthew_hampton]

@v-yuta-msft 

Yes you are correct that the two fact tables are many to many relation**bleep**.

I applied the logic you had below, but did not get the results I expected. When I ran a SUMIF in excel using the same criteria, I got ~10K total matches across all three groups.

Here is the DAX I am using. As I think I mentioned I have a date selected in a filter or will have one selected on a slicer. For purposes of this visual, I do have a date selected (hence the SELECTEDVALUE in the statement).

Deact (Video) = CALCULATE(SUM(FactScorecard[TransactionQuantity]),FILTER(FactScorecard,AND(OR(FactScorecard[TransactionType]="Deactivation",FactScorecard[TransactionType]="Reactivation"),AND(FactScorecard[TransactionSubType]="Voluntary",AND(OR(FactScorecard[FamilyDescription]="Cable",OR(FactScorecard[FamilyDescription]="IPTV Cable",FactScorecard[FamilyDescription]="IP Fiber Cable")),AND(FactScorecard[ChangeDate]>=SELECTEDVALUE(FactChrunPropensityCBT[ScoringDate]),AND(FactScorecard[ChangeDate]<=SELECTEDVALUE(FactChrunPropensityCBT[EndDate]),FactScorecard[AccountNumber] IN VALUES(FactChrunPropensityCBT[AccountNumber]))))))))

Thanks,

Matt