There are two tables from two data sources:
Table1(Field1, Field2, Field3, Field4, Field5, Field7)
Table2(Field1, Field2, Field3, Field7)
Next I collect agregate table "agregate_table" as:
select t1.Field1, t1.Field2, t1.Field3, t1.Field4, t1.Field5, t2.cnt as cnt_all, t1.cnt
from (select Field1, Field2, Field3, Field4, Field5, count(distinct Field7) as cnt
from Table1
group by Field1, Field2, Field3, Field4, Field5) as t1
left join (select Field1, Field2, Field3, count(distinct Field7) as cnt
from Table2
group by Field1, Field2, Field3) as t2 on t2.Field1 = t1.Field1 and t2.Field2 = t1.Field2 and t2.Field3 = t1.Field3
that is, it turns out that the selection from Table1 is more detailed, plus not all rows of Table1 can be found suitable in Table2 In the model, you need to make a measure that reflects the ratio of cnt from Table1 to cnt from Table2, i.e.: sum (cnt) / sum (cnt_all) from my script. But it is impossible to summarize cnt_all, since it does not take into account the distributions over the fields Field4 and Field5, which are in Table1, but not in Table2. I'm going to make separate measures for the numerator and denominator and then a general measure for the ratio. The numerator has a very simple formula because it should be influenced by all fields (Field1, Field2, Field3, Field4, Field5) Is it possible using DAX to implement a measure that calculates sum (cnt_all), taking into account the applied filters only Field1, Field2, Field3 and, moreover, using a unique selection of Field1, Field2 , Field3, sum_cnt (since sum_cnt comes from Table2, and there is a partition by Field1, Field2, Field3) will calculate sum (cnt_all)
on SQL:
select t1.Field1
, t1.Field2
, t1.Field3
, t1.Field4
, t1.Field5
, t1.cnt / t2.cnt_all as ratio
from (
select Field1, Field2, Field3, Field4, Field5, sum(cnt) as cnt
from agregate_table
group Field1, Field2, Field3, Field4, Field5
) as t1
left join (
select Field1, Field2, Field3, sum(cnt_all) as cnt_all
from (
select distinct Field1, Field2, Field3, cnt_all
from agregate_table
) as t
group by Field1, Field2, Field3
) as t2 on t2.Field1 = t1.Field1 and t2.Field2 = t1.Field2 and t2.Field3 = t1.Field3
how to implement it in DAX?
