Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi ,
how find the "same row" with a different value ?
I explain :
How to find in a table lines which have the same employee id, the same name, the same date of birth but a different company where is Active for example ?
In this example the employee Test with the same date of birth , has 2 different companies.
Its a bug.
and the best way to show this?
thanks a lot
Try something like this, in my table color, brand, and Stock type are the column of the Products table, replace these with your table and columns and if you want to add logic then you can create a new variable that refers to the column in the table and use it inside the FILTER function.
SameSame =
VAR OuterProductColor = Products[Color]
VAR OuterProductBrand = Products[Brand]
VAR OuterProductStock = Products[Stock Type]
VAR ProductsWithSameDetails =
FILTER (
Products,
Products[Color] = OuterProductColor
&& Products[Brand] = OuterProductBrand
&& Products[Stock Type] = OuterProductStock
)
VAR CountOfSameProducts =
COUNTROWS ( ProductsWithSameDetails )
RETURN
CountOfSameProducts
If you don't prefer variables then you can use EARLIER, but variables help in readability, the purpose of variables and EARLIER is to distinguish between the column of the products inside the FILTER function and columns in the table and the Row context created by the calculated column on the product table and row context created by FILTER function.
SameSame =
VAR ProductsWithSameDetails =
FILTER (
Products,
Products[Color] = EARLIER ( Products[Color] )
&& Products[Brand] = EARLIER ( Products[Brand] )
&& Products[Stock Type] = EARLIER ( Products[Stock Type] )
)
VAR CountOfSameProducts =
COUNTROWS ( ProductsWithSameDetails )
RETURN
CountOfSameProducts
@Cobra77 , this will give count based on match condition
countx(filter(Table,[emp id] =earlier([emp id]) && [name] =earlier([name]) && [Birthday] =earlier([Birthday])
&& [status] =earlier(status) && [status] =1),[emp id])
rows having count>0 are duplicate
Hi @amitchandak ,
thansk for your answer
in reality in my table I have 50 columns and I especially want to do the test on 3-4 identical columns and which have a different company
I try with this measure in a filtered table on the indicator and a table which gives me the detail after having clicked on the 1st it seems to work.
Hi @Cobra77
Do you still have issues with this?
If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!
Cheers,
Sturla
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |