Forum Discussion
Removing duplicates in the visualisation only
Hi,
I am trying to remove the duplicates only for the visualisations, not in transform data query. So that I will end up with data with all 'cases' and with data with 'outstanding cases' since the duplicates will be removed. Is there a way to filter it somehow?
Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.You can have measures like
Unique Cases = DISTINCTCOUNT('Table'[ID])
Unique Cases =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALLEXCEPT('Table', 'Table'[ID]),
'Table'[Column] = "Value"
)
)
Outstanding Cases =
CALCULATE(
[Unique Cases],
'Table'[StatusColumn] = "Outstanding"
)So, my data contains the total number of cases and some cases have the same reference number. Ideally, I do not want to remove the duplicates in Transform data as I still want to see the total number but I would like to remove them in a report visual itself, for example in a card visualisation. I hope it makes sense.
2 Replies
- amitchandak
Super User
Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.You can have measures like
Unique Cases = DISTINCTCOUNT('Table'[ID])
Unique Cases =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALLEXCEPT('Table', 'Table'[ID]),
'Table'[Column] = "Value"
)
)
Outstanding Cases =
CALCULATE(
[Unique Cases],
'Table'[StatusColumn] = "Outstanding"
) So, my data contains the total number of cases and some cases have the same reference number. Ideally, I do not want to remove the duplicates in Transform data as I still want to see the total number but I would like to remove them in a report visual itself, for example in a card visualisation. I hope it makes sense.