Association and required a common filter between two tables

[Anonymous]

Hi,

I have 3 table i.e., Table 1 , Table 2 and Calendar.

Provided the data model, I have a requirement where i need to associate based on Date. 

The output which i am looking for is when i select July 1 i would like to divide numerator with Denominator. So when i select "AL" in type i want 5/2=2.5.  The problem is i have Type column in both the table, but i want a single column "Type" and if is select that it should give 5/2=2.5. But now i am selecting both (Table1 and Table2) the "type" filter to get 5/2.

If i select Type = AL from table 1 then it is filtering out for table 1 alone. (Not getting the required output)

If i select Type = AL from table 2 then it is filtering out for table 2 alone. (Not getting the required output)

[Greg_Deckler]

So, you are probably going to want a measure like:

Measure = 
VAR __type = MAX('Table 1'[Type])
RETURN
SUMX(FILTER('Table 2',[Type] = __type),[Denominator])

[Anonymous]

Thanks for your help!!

We are getting closer but when i select one date and not selected any "Type" filter at that time it is taking only max values not both.
I want 10/5 without selecting any "Type" filter for July 1 but now it is showing 10/3.

[Greg_Deckler · Accepted solution]

Measure = 
VAR __type = MAX('Table 1'[Type])
RETURN
IF(HASONEVALUE('Table 1'[Type]),SUMX(FILTER('Table 2',[Type] = __type),[Denominator]),SUMX('Table 2',[Denominator]))

[Anonymous]

Thanks!! It is working fine.

I got the output with sample records. Let me try it with Original dataset. Thansk for your help again!!!

[Greg_Deckler]

No problem, happy to help! 🙂