0

I have a PowerPivot table that has a column of IDs and a linked table that contains a set of specific IDs that I want to use to create an indicator variable which I can use to sort on in existing tables and charts. Essentially I want:

If the value in column EpisodeID is found anywhere in LostEpisodes[LostID], then return the value "1", otherwise "0".

LostEpisodes is the linked table and LostID is the column that contains the subset of IDs I want to be able to sort on.

I have tried using =IF(VALUES(LostEpisodes[LostID])=[EpisodeID],1,0) but got an error. Is my syntax wrong or should I be using a different approach? Seems simple enough, but I am new to PowerPivot and DAX.

Thanks

JimG
  • 1
  • is there any detail you can provide about the error you get ? – kommradHomer May 11 '16 at 22:54
  • Thanks kommradHomer. The cells just read #ERROR. There is also a pop-up that reads: The value for column 'LostID' in table 'LostEpisodes' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified. – JimG May 11 '16 at 23:28
  • I found a solution and will post here when I can. Basically it involves a 2-step process with a lookup and an if(isblank – JimG May 12 '16 at 18:21

1 Answers1

0

OK - So I have found an answer that works and wanted to share. Others may have more elegant solutions, but this worked. This is where I miss MATCH.

I have a linked table called LostEpisodes which contains 2 columns, EpisodeID and Lost (all contain the value of 1 as they are all lost episodes). For my purposes I am manually entering the episode IDs as there are only a few. EpisodeID is also in the main table and is the column I am matching on.

I started with one new column labeled LostLookup with the following formula:

=LOOKUPVALUE(LostEpisodes[Lost],LostEpisodes[EpisodeID],[EpisodeID])

I then created a new column with the following formula:

=if(ISBLANK([LostLookup]),"NotLost","Lost")

This creates the indicator variable I can now use in pivot tables and charts. I have tested it and it works great.

Hope this makes sense!

JimG
  • 1