Distinct dax with condition

[Anonymous]

Hi,

I'm getting unique values using Distinct to avoid duplicate list but can I add one conditon from its data table?

In the same table 'TOSS serivce PO_master - Append', there's another column 'Contract Amount' and I wanna take out which has value 0.

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

Can you please try the below DAX?

VAR FilteredTable = FILTER(
'TOSS service PO_master - Append',
'TOSS service PO_master - Append'[Contract Amount] <> 0
)
RETURN DISTINCT(SELECTCOLUMNS(FilteredTable, "Service No", 'TOSS service PO_master - Append'[service no]))

If this answers your questions, kindly accept it as a solution.
If you found this helpful, then give kudos.

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

Can you please try the below DAX?

VAR FilteredTable = FILTER(
'TOSS service PO_master - Append',
'TOSS service PO_master - Append'[Contract Amount] <> 0
)
RETURN DISTINCT(SELECTCOLUMNS(FilteredTable, "Service No", 'TOSS service PO_master - Append'[service no]))

If this answers your questions, kindly accept it as a solution.
If you found this helpful, then give kudos.

[Anonymous]

It works thanks!

Can we create Contract Amount column with your code?
Lookupvalue dax not working cuz it only get 1 First or Last value but I wanna get Sum of 'Contract Amount' from 'TOSS service PO_master - Append' table into current Distinct table using same 'Service No.'

[mdaatifraza5556]

Hi @Anonymous 

Can you please try this?

VAR FilteredTable =
FILTER(
'TOSS service PO_master - Append',
'TOSS service PO_master - Append'[Contract Amount] <> 0
)

RETURN
ADDCOLUMNS(
DISTINCT(SELECTCOLUMNS(FilteredTable, "Service No", 'TOSS service PO_master - Append'[service no])),
"Total Contract Amount",
CALCULATE(
SUM('TOSS service PO_master - Append'[Contract Amount]),
ALLEXCEPT('TOSS service PO_master - Append', 'TOSS service PO_master - Append'[service no])
)
)

If this answers your questions, kindly accept it as a solution.
If you found this helpful, then give kudos.

[Anonymous]

Can you please take a look the error? 

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

As i have tried with sample dataset

Dataset

Service No.Contract Amount 101 5000 102 7000 103 0 101 3000 104 8000 102 2000 105 0 106 12000 107 4000 108 0 103 0 104 3000 109 5000

I have written the same DAX, and it return the result Can you please recheck the DAX code you wrote?

[Anonymous]

oh this creating 2 columns in one go, right? 

Now it seems working great

[mdaatifraza5556]

Yes 

If I answer your questions, kindly accept it as a solution
If this is helpful then give kudos.