Table Visual - Row duplicates with new calculated column

[clock0928]

Hi all,

I am working on a table visual that seems to be functioning exactly how I want it to... Until I add an additional feature of a calculated column.

Below is my current table - 

Where I then want to add a column at the end, which identifies the last two columns (Calculate & Incoming) and returns a "Match" or "No Match"

Column formula is - IF('Incoming Stats'[Incoming] = [Calculate], "Match", "No Match")

Table with additional column -

I have a bit of data intertwined in the table with multiple data sets, listed below -

- Team Members (First column, can't include due to TM names)

- Calendar (WeekOfYear)

- Work On Hand (wkA &WkB (WOH))

- Daily Stats (wkB (Completed))

- Incoming Stats (Calculate & Incoming)

Daily Stats, Incoming Stats & Work On Hand are linked to both the Calendar and Team Members table via "1 to Many".

Is there anything that I am missing on why this may be causing a duplicate row for each item when adding the additional column?

[danextian]

Hi @clock0928 

What happens is you are comparing a measure, [Calculate], which doesn't have  a row context by default,  against a row value in a column which has a row context. Since the measure doesn't have row context, it is as if there are no filters applied to it (think about its value when it is placed in a blank canvas without any slicers/filters at all). So you either compare [Calculate] against another measure or apply a filter to it.

MEASURE vs MEASURE as a measure =
    [Calculate] = SELECTEDVALUE ( 'Incoming Stats'[Incoming] )

Filtered Measure vs Column as a calc column =
CALCULATE (
    [Calculate],
    FILTER ( 'table', 'table'[Column] = EARLIER ( 'table'[Column] ) ... etc
 )
)
    = EARLIER ( 'table'[Another Column] )

[clock0928]

Thank you for the reply @danextian , however I am still quite new to Power Bi. When you say "So you either compare [Calculate] against another measure or apply a filter to it." - how would I complete this and get what I have working?

[danextian]

 Hi @clock0928 ,

A row context is automatically created when you use a measure in a visual - the per row result depends on the dimensions put in a visual.  For example, a column with one unique value will return a single result and your measure will return just the result for that, that is for the whole of that column. Now, if you add another column, say with five unique values, each value will return its own result.

Using your own example, if you use Incoming Stats'[Incoming] in a visual, that will return the unique row value for that column in relation to other columns put in a visual. So if that unique value is "A", SELECTEDVALUE ( 'Incoming Stats'[Incoming] ) will return "A" and the complete measure will return "match" if [CALCULATE] is SELECTEDVALUE.  This measure is evaluated based on the visible rows.

Your original formula is comparing a measure that doesn't have a row context by default against each row value in 'Incoming Stats'[Incoming]. Try creating a calculated column with just [CALCULATE] and you will know why you're getting an unexpected result.