Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I have a column with my sell ID.
ID |
00065862 |
00035306 |
00049674 |
00038281 |
00036700 |
00053195 |
00032684 |
00078092 |
00049675 |
00078095 |
00048102 |
00048101 |
00048103 |
00038598 |
00035305 |
00035304 |
00035306 |
00043491 |
00036782 |
00082791 |
00039057 |
00032684 |
00048101 |
I have to insert a new column to locate the Repeated ID Value, but, after that, I have to exclude one of it (I will do it with a filter) and so, is possible to check just one of the two or more values repeated?
I attache an example
ID | repeated IDs |
00065862 | |
00049674 | |
00049674 | 1 |
00038281 | |
00032684 | |
00053195 | |
00032684 | 1 |
In my example repeating IDs are 00049674 and 00032684 but I have to check just one of each, because after I will filter out all 1. Is not important if I sign the first or the second of repeated value.
Solved! Go to Solution.
You could use a calculated column like this @Carlo1975
Instance Count =
VAR varCurrentID = 'Table'[ID]
RETURN
COUNTROWS(
FILTER(
ALL('Table'),
'Table'[ID] = varCurrentID
)
)
Same as a COUNTIFS() in Excel.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou could use a calculated column like this @Carlo1975
Instance Count =
VAR varCurrentID = 'Table'[ID]
RETURN
COUNTROWS(
FILTER(
ALL('Table'),
'Table'[ID] = varCurrentID
)
)
Same as a COUNTIFS() in Excel.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI'm not clear on your requirements exactly @Carlo1975 , but you cannot just check a few items to answer that specific question. Power BI, either in Power Query or DAX, will check the entire dataset based on the formula you use.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk, I understand, ans so, more simple. I have to add a column and check when a Id is repeated. Like if in exel i use CountIf
Hi @Carlo1975
If you create a calulcated table with
ALL(Table1[ID])
the duplicates will already be removed
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.