Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aster1423
Frequent Visitor

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? 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

View solution in original post

aster1423
Frequent Visitor

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. 

View solution in original post

2 REPLIES 2
aster1423
Frequent Visitor

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.