Forum Discussion

Rasmusrock's avatar
Rasmusrock
Helper II
9 years ago
Solved

DAX help

  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 t...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

     

    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

DataChant's avatar
DataChant
Most Valuable Professional
9 years ago

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's avatar
Rasmusrock
Helper II
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

     

    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