Writing a measure that returns True/False if a column value appears in another table? At row level

[Anonymous]

I've ran into a problem that's a bit difficult to spell out, but I'll do my best.

I have an excel sheet I'm bringing into Power BI that represents individual capital projects and budget information about those capital projects. It's title is "FY18-20 Plan". I have many other data sources that also represent individual capital projects / expenditures with budget information. There are duplicates in these tables. Essentially, my client reports financial information from many different sources, and I'm trying to build a tool that aggregates all of the financial information from the different sources.

I am trying to add dummy variables to my excel source that returns "True" if the project appears in one of the other data sources, and "False" if it doesn't. I initially created Calculated columns that did this just fine. However, the Calculated columns created a circular dependency error when I tried to filter to add a two-way filter in the relationship in the data model. This type of relationship is neccessary for a number of measures I have written to work correctly. See screenshot of the model here:

Is there a way to write a measure that returns this same type of functionality? I will need multiple measures that function similarly for all of the other data sources I have. The DAX for the calculated column looked like this:

Is In MS Project? =

CALCULATE(

COUNTROWS(Projects),

FILTER(Projects, Projects[CER]='FY18-20 Plan'[CER #])

) >0

which worked fine, but as a measure, I receive the error "A single value for column 'CER #' in table 'FY18-20 Plan' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

When getting errors similar to this before, I can usually use a SUMx to fix it, but nothing I try is helping in this case. Any ideas on how to write this measure? Let me know if more info or a clearer explanation is needed.

[edhans]

Try this @Anonymous - you need to compare your filter to a scalar value. MAX will convert the column to that.

Is In MS Project? =
CALCULATE(
    COUNTROWS( Projects ),
    FILTER(
        Projects,
        Projects[CER]
            = MAX( 'FY18-20 Plan'[CER #] )
    )
) > 0

I'd need data to play with this further.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

@edhansthank you... it almost works. The only problem is that for the MAX value in the Projects table, that one is showing up as FALSE when it should be TRUE. All of the values where the CER is lower than this max value are TRUE which is correct.

Would you happen to know an easy workaround / addition to this formula?

[edhans]

As stated, I'd need data to see. I cannot create measures and know what they will return without underlying data samples.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum