How to properly sum measures?Sum two measures

[Anonymous]

Hi everyone!

I'm trying to make a distinct count on a field on two tables that are not related. I'm making a distinct count and as a result, I have mesure1 for table1 and measure2 for table2 where if I group by customer I got a 1 for every customer in each table. Some cases measure1=1, measure2=0; measure1=0, and measure2=1 both cases it's ok because If I add the measures I get what I want (result=1). The problem is when I got measure1=1 and measure2=1 I add and get a 2 (I will be counting June customer twice) 

I made a third measure

Total  =

var Total = [measure1]+[measure2]

return

IF(Total=2;Total/2;Total)

but the result is not what I wanted

 PowerBI

I want the sum of the Total column, not the sum of Total measure1 + Total measure2

Excel (Sum each column)

As I don't have the data in tables but both are measures I can't use HASONFILTER or COUNT ROWS or SUMX and I don't know what else to do

Thanks in advance for your help.

Best regards.

Mijalis

[Greg_Deckler]

Try something along the lines of:

Measure =

COUNTROWS(

  DISTINCT(

    UNION(

      SELECTCOLUMNS('Table1',"Column",[ColumnFromTable1]),

      SELECTCOLUMNS('Table2',"Column",[ColumnFromTable2])

    )

  )

)

[VijayP]

Try to create another Tabel using Summarise function so that you can have the columns in one table with respective values so that you can use single measure (distinctcount) to get the result the way you want

[Anonymous]

Hi thx for your reply

I used summarized and it worked perfectly.

Thank you very much!

Best regards,

Mijalis

[az38]

@Anonymous 

IF(Total=2;Total/2;Total)

this always will give 1 based on you data sample and Im pretty sure 74 is a good sum for your data

how is your data look like in original data source? what result do you expect?

[Anonymous]

Hi, thx for the quickanswer.

I want 74 as a result, the problem is that power bi is giving 111, wich is the sum of total measure1 + total measure2. 74 will be the sum of the total column.