Forum Discussion

wvadik's avatar
wvadik
Icon for Helper III rankHelper III
5 years ago

DAX sum by distinct values

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?

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Provide sample data and expected outcome.

     

    Note that both ALL() and VALUES() give you distinct collections.  As does SUMMARIZE()

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi wvadik ,

     

    You can use 'Merge Queries' to combine the two tables first.

     

    Best regards,
    Lionel Chen

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