Removing duplicates in a month over month view

[diannedentice]

Hello,

Kind of a unique situation in which I'll do my best to explain.

• I am connected to a data cube so many features are not available such as Power Query, Grouping, Adding Columns, removing duplicates feature,  just to mention a few
• And I have no direct access to this cube, only through Power BI.

Situation:  Our Opportunities have cases.  An opportunity can have multiple cases opened at any time.  We'd like to capture ONLY the 1st case so looking at a month by month view, you wouldn't see the amount duplicated. 

Example:

As you can see, the built in logic works in a table view, but it does not when looking month over month so when our users add up the month over month totals, it doesn't tie with the total.  Is there a way to create a measure (again, we can't create a calculated column) that would just show the amount in Nov? We want to capture the first occurrence of this opportunity number only.  

 Thank you.

[Anonymous]

@diannedentice 

You can just select earliest date in the fields panel, no need to create a measure.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @diannedentice ,

You need to create a measure similar to this one:

Measure = CALCULATE(FIRSTNONBLANK('Table'[AmountUSD]; SUM('Table'[AmountUSD])); ALLSELECTED('Table'[Oportunity number]))

Be aware that the last part ALLSELECTED may need to be changed based on your data to a filter or something similar.