Forum Discussion
create a calculated column while ignoring row context
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
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.:smileyhappy:
Regards
4 Replies
Hi sf_,
Could you try the formula below to see if it works in your scenario?:smileyhappy:
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
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!
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.:smileyhappy:
Regards
