How to use distinct and get the unique values in created measure (expression) by account name?

Anonymous — Wed, 31 Jul 2024 14:03:35 GMT

Hi Experts ,

I am stuck with the scenario below :

Recently i have applied unpivot columns for few columns to rearrange the rows & attributes.

After that most of the columns are duplicated (expected) , I have fixed few values related to that columns by using DISTINCT dax , but I cannot use the same DISTINCT into the measure which i have created to summing up the values and with using some other measures as below :

Revenue & Margin = SUMX('Delivery Updates',[Revenue*Margin]) --> [Revenue*Margin] is an another measure which result is OK.

The result should be : 51246 euro (1 Account)

But once i did unpivot columns 1 row of account was duplicated as 27 rows with same account name as below :

before unpivot :

after unpivot : (27 rows)

Now the case is I cannot use DISTINCT to get the 1 Account value from this measure.

Now it is showing below huge number : total 27 rows = 27*51246

Please help to fix this part asap 😞

Due to this the total margin% is impacted ..

Thanks

Re: How to use distinct and get the unique values in created measure (expression) by account name?

Anonymous — Thu, 01 Aug 2024 05:39:17 GMT

Hi, Anonymous

As an example, does your sample data look like this?

Maybe you can try this formula.

Measure = VAR _table=DISTINCT('Table') RETURN SUMX(_table,[Value])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: How to use distinct and get the unique values in created measure (expression) by account name? in DAX Commands and Tips

How to use distinct and get the unique values in created measure (expression) by account name?

Re: How to use distinct and get the unique values in created measure (expression) by account name?