How to filter distinct values by other columns using Power BI DAX

[Anonymous]

Hello guys,

I have the data below:

Id     Date      Value

10195  6/4/2020  ABC

10225  19/5/2020 ABC

10257  18/6/2020 ABC

10280  20/7/2020 ABC

10280  20/7/2020 ABC

10280  20/7/2020 ABC

10280  20/7/2020 ABC

10299  30/7/2020 ABC

10299  30/7/2020 ABC

10299  30/7/2020 DEF

10299  30/7/2020 ABC

10299  30/7/2020 ABC

10299  30/7/2020 ABC

Using DAX in Power BI, I would like to calculate the amount of values by id and date. The result table must be like this one:

Id.    Date      Value

10195  6/4/2020  ABC

10225  19/5/2020 ABC

10257  18/6/2020 ABC

10280  20/7/2020 ABC

10299  30/7/2020 ABC

10299  30/7/2020 DEF

Thanks in advance!

Best regards,

Gustavo

[Fowmy]

@Anonymous 

You cam go to Modeling Click on  New Table and paste the following code to get the result as a table:

Distinct Table = DISTINCT(Table) 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mahoneypat · Accepted solution]

Three ways to do that (at least). 

1 - You can do it in the query editor by adding a GroupBy step in the ID column, clicking on Advanced in the Group pop up and add grouping to the other 1 or 2 columns. 

2 - Also in the query editor, you could add a step with Remove Duplicates and update the  Table.Distinct( ) with the list of columns to summarize by.

3 - You can make a DAX calculated table with

New Table = SUMMARIZE(Table, Table[ID], Table[Date], Table[Value])

Regards,

Pat