Calculated Column not Filtering

[JBorja]

Hello all,

I have the following calculated column in table 'SO Issues  Key':

Count Issues 2 = 
VAR a = CALCULATE(
    COUNTROWS(SO_Detail), 
        FILTER(SO_Detail, 'SO Issues Key'[SO Line Notes Designator] = SO_Detail[Current Issue 1] || 'SO Issues Key'[SO Line Notes Designator] = SO_Detail[Current Issue 2] || 'SO Issues Key'[SO Line Notes Designator] = SO_Detail[Current Issue 3])
    )
RETURN IF(
    ISBLANK(a), 
    0, a
    )

'SO Issues Key' has a one to many relationship to 'SO_Detail' by the columns [SO Line Notes Designator] and [Current Issue 1] , respectively, and separate inactive relationships between the same 'SO Issues Key' column and [Current Issue 2] and [Current Issues 3].

For one row, this calculated column should = 50. When I apply a filter to column in 'SO Detail', that same column should = 1, but it stays at 50. Can anyone help with this?

Thanks!

[Ritaf1983 · Accepted solution]

Hi @JBorja 
Calculated columns aren't working with filters.
They are a kind of "static"

All logic that you need "on the flight" shoul be calculated with the measures.

For a more detailed solution 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous · Accepted solution]

Hi  @JBorja ,

Here are the steps you can follow：

1. Create measure.

All =
var _select=SELECTEDVALUE('SO Detail'[Account Mgr])
var _CONCATENATEX=
CONCATENATEX(
    FILTER(ALL('SO Detail'),
    'SO Detail'[Account Mgr]=_select),'SO Detail'[Current Issue(s)],",")
return
DIVIDE(
LEN(_CONCATENATEX)-
LEN(
SUBSTITUTE(_CONCATENATEX,MAX('SO Issues Key'[SO Issues]),"")),LEN(MAX('SO Issues Key'[SO Issues])))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @JBorja 
Calculated columns aren't working with filters.
They are a kind of "static"

All logic that you need "on the flight" shoul be calculated with the measures.

For a more detailed solution 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[JBorja]

Hi @Ritaf1983 ,

Thanks for you response. I tried using a measure instead, and I got an error when loading into a visual, which states "MdxScript(Model)(7, 71)...A table of multiple values was supplied where a single value was expected".

Here's the code used in the measure:

Count Issues =
VAR x = SUMMARIZECOLUMNS('SO Issues Key'[SO Issues])
VAR a = CALCULATE(
    COUNTROWS(SO_Detail), 
        FILTER(SO_Detail, CONTAINSSTRING(SO_Detail[Current Issue(s)], x)
    )
)
RETURN IF(
    ISBLANK(a), 
    0, a
    )

I'm still not sure what I'm messing something up.

Here's an example of my data tables:

SO Issues Key

SO Issues	Count Issues (measure/calculated)
Pricing	3
Credit	4
Shipping	3

SO Detail

Account Mgr	SO	Current Issue(s)
Alex	1111	Pricing, Credit
Brandon	2222	Shipping
Alex	3333	Pricing
Alex	4444	Shipping, Credit
Emily	5555	Pricing, Credit, Shipping
Emily	6666	Credit

If I filter for Emily, I would expect the SO Issues Key table to look like this:

SO Issues	Count Issues (measure/calculated)
Pricing	1
Credit	2
Shipping	1

Does it still make sense to use a measure for this data? If so, what's wrong with the formula I used?