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 am trying to create a Column that only show the value if is duplicate on the first line. rather than repeating the distinc count on every single row.. I added the earlier on ID and also on RowNumber but get same results.
RowNumber | ID | DistinctCount | Show only Once |
1 | 5343 | 2 | 2 |
3 | 5343 | 2 | |
4 | 4333 | 2 | 2 |
5 | 4333 | 2 | |
6 | 1333 | 1 | 1 |
Solved! Go to Solution.
There might be a faster way to do this, but this builds each step one at a time @Anonymous
First Distinct =
VAR varCurrentID = [ID]
VAR varCurrentRow = 'DataTable'[RowNumber]
VAR varFirstRow =
MINX(
FILTER(
'DataTable',
'DataTable'[ID] = varCurrentID
),
'DataTable'[RowNumber]
)
VAR varCounts =
COUNTX(
FILTER(
'DataTable',
'DataTable'[ID] = varCurrentID
),
'DataTable'[ID]
)
RETURN
IF(
varCurrentRow = varFirstRow,
varCounts,
BLANK()
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank You for quick response...Edhans😀
Glad to help @Anonymous
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThere might be a faster way to do this, but this builds each step one at a time @Anonymous
First Distinct =
VAR varCurrentID = [ID]
VAR varCurrentRow = 'DataTable'[RowNumber]
VAR varFirstRow =
MINX(
FILTER(
'DataTable',
'DataTable'[ID] = varCurrentID
),
'DataTable'[RowNumber]
)
VAR varCounts =
COUNTX(
FILTER(
'DataTable',
'DataTable'[ID] = varCurrentID
),
'DataTable'[ID]
)
RETURN
IF(
varCurrentRow = varFirstRow,
varCounts,
BLANK()
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |