DAX help

[Rasmusrock]

Hi all,

I am looking for help with a DAX formula, and was hoping someone here could help me.

To set the context, i have data from two tables (purchasing lines and stock lines), and i want to make a table of how many unique creditors are active across the two tables.

So right now, i use the DISTINCTCOUNT formula on creditor number to get the unique creditors that are active in each table, however, the problem comes when a creditor is active in both tables, as shown below:

Country	Kreditorer (indkøb)	Kreditorer (lager)	Antal aktive kreditorer
Danmark	9	13	22
Creditor 1	1	1	2
Creditor 2	1	1	2
Creditor 3		1	1
Creditor 4		1	1
Creditor 5	1	1	2
Creditor 6	1	1	2
Creditor 7	1	1	2
Creditor 8	1	1	2
Creditor 9		1	1
Creditor 10		1	1
Creditor 11	1	1	2
Creditor 12	1	1	2
Creditor 13	1	1	2

So, the above table shows the active creditors for the country Denmark:

There are 13 unique creditors, all in all, 9 in the purchasing lines and 13 from the stock lines. However, when i add the two measures together, is says there are 22 unique creditors active, as some of them are active in both tables..

So can anyone help me with a formula that combines the two DISTINCT count formulas, but counts creditors that are active in both tables as one?

Home someone can help,

/Rasmus

[DataChant]

Can you provide more information on the table schemas you use?

Assuming you have Table1 and Table2 with columns ID for the creditors IDs, this measure can count the unique IDs

Unique IDs = COUNTROWS ( DISTINCT ( UNION ( VALUES ( Table1[ID] ) , VALUES (Table2[ID] ) ) ) )

[Rasmusrock]

Thanks for your reply @DataChant.

I have two tables, each with a column showing the creditor ID.

I tried using your expression, however, as i am using Excel 2010, the 'UNION' function does not work. Is there a counterpart, or some kind of a workaround to this function in powerpivot 2010?

Best regards,

/Rasmus

[v-huizhn-msft · Accepted solution]

Hi @Rasmusrock,

I try to reproduce your scenario using the following table.

You'd better merge the table into one new table using PowerQuery based on the Creditor ID. In the Power Query Editer window, click the Combine->Merge, you will get the snapshot below.

 

Please click load&close, import the new table into model. Then create a calculated column using the formula in table.

=IF(Table2[Kreditorer(indk)]||Table2[NewColumn Kreditorer (lager)],1,0)



Then you can count the active creditor based on the calculated coulmn as follows. 

Unigue ID:=CALCULATE(DISTINCTCOUNT(Table2[Creditor ID]),FILTER(Table2,Table2[New]=1))

Best Regards,
Angelia