Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 Reporting