create a calculated column while ignoring row context

[sf_]

Dear community 

I'm having an issue with Power BI (desktop). Here's a small example to illustrate: 

I have two Tables, A and B. Table A consists of two columns: code and product. Each code and each product occurs only once. E.g.: 

Code	product
code1	car
code2	bike
code3	motorcycle

Table B contains a product column as well. All products of A occur in B as well. There is also a column type. E.g.: 

Product	type
Car	typeA
bike	typeA
motorcycle	typeA
cat	typeB
dog	typeB

Now I want to use a report level filter on the code of Table A. I intend to filter either to one product or not filter at all. I now want to add a calculated column to Table B, specifying whether the product of typeA is included, based on the active filter on Table A. The products of other types than typeA should be included (this can be done by first checking the products on type and map all those not of typeA to true). E.g. when the filter on code of Table A is not active, I need the following result: 

Product	Type	Is included
Car	typeA	true
bike	typeA	true
motorcycle	typeA	true
cat	typeB	true
dog	typeB	true

When the filter is set to code2, I need: 

Product	Type	Is included
car	typeA	false
bike	typeA	true
motorcycle	typeA	false
cat	typeB	true
dog	typeB	true

Although this seems like a simple problem, I've run into quite some trouble. I've succeeded in creating a measure that keeps track of the filtered product ignoring row context of Table A, yet if I use this measure to create a calculated column using string comparison, the value of the measure alters (I assume because of the new row context in Table B?) and the results are wrong. I've been messing around with 'allselected()', 'keepfilters()' etc, but haven't been able to fix it so far. Anyone who knows how to solve this? All help is greatly appreciated! 

Best wishes

[v-ljerr-msft]

Hi @sf_,

Could you try the formula below to see if it works in your scenario?

Is Included =
VAR selectedType =
    CALCULATE (
        FIRSTNONBLANK ( 'Table B'[type], 1 ),
        FILTER (
            ALL ( 'Table B' ),
            'Table B'[Product] = FIRSTNONBLANK ( 'Table A'[product], 1 )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Table A'[Code] ),
        IF (
            FIRSTNONBLANK ( 'Table B'[Product], 1 )
                = FIRSTNONBLANK ( 'Table A'[product], 1 )
                || FIRSTNONBLANK ( 'Table B'[type], 1 ) <> selectedType,
            TRUE (),
            FALSE ()
        ),
        TRUE ()
    )

Note: Make sure there is no any relationship between Table A and Table B.

Regards

[sf_]

Dear @v-ljerr-msft

Thank you for the suggestion! This indeed works fine as a measure -- I managed to create a similar one with the same result --- but the problem is that I need a calculated column. Unfortunately, this does not work as a column:

Any ideas how to resolve that?

Best wishes and thank you again for your help!

[v-ljerr-msft · Accepted solution]

Hi @sf_,

Not like measures, calculate columns are computed during the database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report. So I don't think this can be done with a calculate column in this case.

Regards

[sf_]

Dear @v-ljerr-msft

Ok, that's good to know, thank you again for your help!

Best wishes