topic DAX formula to add values only if they are not present in DAX Commands and Tips

DAX formula to add values only if they are not present

Koushal — Sat, 09 Oct 2021 03:49:36 GMT

Please can you help me with the DAX formula to identify values that are not already present?

I am working with 3 tables Q1, Q2, and Report

I have managed to take distinct customers from Q1 and Q2 and created a new table called Report and then I have added the balances of Q1 to the report.

Now I want to populate balances from Q2 only if balance from Q1 is not transferred and customer is 5 years +

The table structure for Q1 and Q2 is

Customer Number

Number of years as a customer- 5 years and above, 3 and 5 years,

Balances - Points gathered

Re: DAX formula to add values only if they are not present

Fowmy — Sat, 09 Oct 2021 08:33:23 GMT

Koushal

Can you share some sample data with the desired output to have a clear understanding of your question? Mention whether you want a calculated column or measure
You can save your files in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

Re: DAX formula to add values only if they are not present

Koushal — Sun, 10 Oct 2021 13:37:53 GMT

Table 1

CUSTOMER ID	BALANCE	Classification	Payment Status
12345	8,273.00	5 years	Transferred
7836383	2,992.00	3 years	Not eligible
3823873838	3,933.00	2 years	Not eligible
293793739	33,332.00	4 Years	Not eligible

Table 2

CUSTOMER ID	BALANCE	Classification	Payment Status
45736393	92,162,563.00	5 years	Yet to transfer
897828	890.00	3 years	not eligible
928292	98,799.00	2 years	not eligible
29282	9,899.00	1 years	not eligible
12345	922.00	5 years	not eligible

Report

- we need to update this table with the balance from Q2 only if the classification is "5 years" and the payment status is not "Transferred"

CUSTOMER ID	BALANCE	Classification	Payment Status
12345	8,273.00	5 years	Transferred
7836383
3823873838
293793739
45736393
897828
928292

Re: DAX formula to add values only if they are not present

Koushal — Sun, 10 Oct 2021 13:38:43 GMT

Fowmy - sorry forgot to tag you

Re: DAX formula to add values only if they are not present

Fowmy — Sun, 10 Oct 2021 14:11:49 GMT

Koushal

Not sure sure if you need the following output:

Create a new table with this code:

UNION( FILTER( Table1 , Table1[Classification] = "5 Years" && Table1[Payment Status] = "Transferred" ), FILTER( Table2 , Table2[Classification] = "5 Years" && Table2[Payment Status] = "Transferred" ) )