cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PowerBIplane Frequent Visitor
Frequent Visitor

Remove duplicates based on values

I am very new to BI and i am tying myself in knots.

 

I have the following set of data:

 

Example1a.png 

 

I have to filter the set so anything with a Service_ID equal to 133 or 134 is kept

 

If another row has a Service_id is 133  or 134 but the Event_id is equal to a row with the same Event_id and an invalid service_id this row is also Filtered out.  So the data should look like the following:

 

Example1b.png

 

I can filter down but cannot work out the syntax to evaluate

 

Hope this explains

 

regards

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Remove duplicates based on values

I believe what you want is something like:

 

Measure 6 = 
VAR __eventID = MAX([Event_ID])
VAR __table = ADDCOLUMNS('Table4',"__count",COUNTX(FILTER(ALL('Table4'),[Event_ID] = EARLIER([Event_ID]) && [Service_ID]<>133 && [Service_ID]<>134),[Service_ID]))
RETURN
IF(SUMX(__table,[__count]) = 0,1,0)

See Page 6, Table4


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Remove duplicates based on values

I believe what you want is something like:

 

Measure 6 = 
VAR __eventID = MAX([Event_ID])
VAR __table = ADDCOLUMNS('Table4',"__count",COUNTX(FILTER(ALL('Table4'),[Event_ID] = EARLIER([Event_ID]) && [Service_ID]<>133 && [Service_ID]<>134),[Service_ID]))
RETURN
IF(SUMX(__table,[__count]) = 0,1,0)

See Page 6, Table4


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


PowerBIplane Frequent Visitor
Frequent Visitor

Re: Remove duplicates based on values

thanks for that.  it done what I required.