Multiple values Duplicating Rows

[DandyTheBeauty]

Hi all - having an issue where I have a column from one table (OpportunitiesView[OpportunityName]) and a column from a different table (CalendarEventsView[Subject]).  As you can see the table is showing the OpportunityName for every instance of Subject (see Monarch White Marsh as an example).  I would like to only have one line for OpportunityName with the Last instance of Subject showing up (there is no date/time field related to Subject).  See below:

When I change this to show "Last" it takes the last item in the Subject column and applies it to every OpportunityName, like this:

I'm guessing this has something to do with the relationship of the tables but I'm a bit of a novice with this stuff.  Any insights would be appreciated.  Thanks!

[Anonymous]

Hi, @DandyTheBeauty 

Have you solved your problem? If not resolved, can you tell us your desired output?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

Hi @DandyTheBeauty 

Attached is a PBIX file with an output that I hope you are after.  I've added it as a Calculated Column and as a Measure for you.

The measure is as follows (and calculated column is identical, too):

LastSubjectMeasure = 

CALCULATE (
    LASTNONBLANK ( OpportunitiesView[Subject] , 1 ) ,
    ALLEXCEPT ( OpportunitiesView , OpportunitiesView[OpportunityName] )
)

As you can see in the output, there is only now a single instance.

Hope this helps 🙂

Theo

[DandyTheBeauty]

Thank you for your response Theo, but this gives me the same result as in my first screenshot above.  I think the problem is that in your measure you have OpportunitiesView[subject].  Subject is not in OpportunitiesView, it is in a separate table called CalendarEventsView.  Accordingly, I changed your code above to:

LastSubjectMeasure =

CALCULATE (

    LASTNONBLANK ( CalendarEventsView[Subject] , 1 ) ,

    ALLEXCEPT ( OpportunitiesView , OpportunitiesView[OpportunityName] )

)

This gives me the same result as in my orginal post.

[TheoC]

Hi @DandyTheBeauty 

I'm not sure that it provides you with the same output as your screenshot.  In the PBIX file I provided, my measure only provides the first instance where yours provides all instances.

For example, if you look at Opportunity Name on the left, in your version, you have three records for Monarch White Marsh in your version. However, in my measure, there's only one instance which is the latest version.

If this is not what you're after, could you kindly explain the output you are wanting?

Thanks heaps,

Theo

[DandyTheBeauty]

Hi @TheoC - I appreciate your continued engagement.  What you have shown above is exactly what I'm looking for.  However, in your PBIX file, for both the measure and column that you created you have the following:

As you can see - the 'Subject' column is not in the OpportunitiesView table.  It is in the CalendarEventsView table, and so this won't work.  In your PBIX file you have the 'Subject' column in the OpportunitiesView table.   

[TheoC]

Hi @DandyTheBeauty 

The red underscore in your image is suggesting that the column Subject does not exist in the OpportunitiesView table in my PBIX file. It's likely this was removed / adjusted accidentally.

I have double checked the attached PBIX version so it won't have this error.  Not sure why it appeared on your version but hopefully that is going to be sorted in the attached.

Thanks heaps and glad it helped!

Theo 🙂