DAX ignore\remove records when ID occurs multiple times

[Kaysa]

Hi there

I would like to ignore records on a certain condition.

When is ID occurs multiple times and the Managers column value is equal to the resource value, then that record must be ignored. (examples 10, 11, 13)

But when the ID does not occur multiple times and the Manager value is equal to Resource value then it must not ignore. Examples (14,12,55)

[twi1 · Accepted solution]

Hi @Kaysa  you can add a calculated column to flag warn and show the records to be ignored. Use this warn to filter out the records in your visual or table.
 You can do this by creating a new column with the following DAX expression:Warn =
IF (
CALCULATE (
COUNTROWS ( Orders ),
ALLEXCEPT ( Orders, Orders[ID] )
) > 1
&& Orders[Managers] = Orders[Resource],
1,
0
)
This column will have a value of 1 for the records that should be ignored and 0 otherwise.Now, you can filter out the records in your visual or table by adding a filter to exclude the warned records:
FilteredOrders =
FILTER (
Orders,
Orders[Warn] = 0
)

You can then use this FilteredOrders table in your report to show the data without the ignored records.

another way is here: Create a new calculated column:

Use DAX to create a calculated column that will warn the rows to be ignored based on the conditions you specified.

Here’s the DAX formula to create the calculated column:IgnoreRows =
VAR NewID = Table1[ID]
VAR NewManager = Table1[Managers]
VAR NewResource = Table1[Resource]
VAR MultipleIDCount = COUNTROWS(FILTER(Table1, Table1[ID] = NewID))
RETURN
IF(
(MultipleIDCount > 1 && NewManager = NewResource),
1,
0
)Filter the table based on the new column:You can now use this calculated column to filter out the records you want to ignore. You can apply this filter in your reports or visualizations to exclude the warned records.Go to the visualization pane and select the visual you want to filter.put the IgnoreRows column to the Filters pane.Set the filter to show only rows where IgnoreRows is 0.

Let me know if you need further assistance! and if it helped you

[Kaysa]

Thank you, your solution work as you described