Removing duplicates in the visualisation only

[Anonymous]

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? 

[amitchandak · Accepted solution]

@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"
)

[Anonymous · Accepted solution]

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. 

[amitchandak · Accepted solution]

@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"
)