Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Distinct dax with condition

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.

General Comment

Tips and Tricks

mdaatifraza5556
1 year ago
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

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?

7 Replies

mdaatifraza5556
Super User
1 year ago
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
  Not applicable
  1 year ago
  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
    Super User
    1 year ago
    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.