cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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?

2 REPLIES 2
Community Support

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.

Super User

Provide sample data and expected outcome.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.