Calculated Column across row context

[vgeldbr]

I'm stumped. I want to create a calculated column for purposes of filtering and slicing the table but I need the value to consider other rows in the table. Consider the table below:

I would like to add a column that indicates for any given Product if there is any projects related to that product where LeadershipRptInvestGroup = "MFS Production Cloud". I've tried all combinations of filtering, keeping filters etc. and cannot success. I'd be happy with a simple output of 1 or 0. So for Product A I would expect both rows to show 0 and for Product B I would expect all rows to show 1 (because one of the projects has MFS Production Cloud as a value under LeadershipRptInvestGroup.

[ValtteriN · Accepted solution]

Hi,

For this kind of filtering I would create a filter measure and apply it to your visual.
So e.g. here we only keep rows where our item's material is Wood

Filter when item =

var _item = MAX(ItemExample[Item]) return

IF(COUNTROWS(FILTER(all('Matrix example'),'Matrix example'[Item]=_item&&'Matrix example'[Material]="Wood"))>0,1,0)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[PaulOlding · Accepted solution]

Hi @vgeldbr 

Here's some DAX for a calculated column

Is MFS = 
VAR _Rows = 
CALCULATE(
    COUNTROWS(Products),
    Products[LeadershipRptInvestGroup] = "MFS Production Cloud",
    ALLEXCEPT(Products,Products[Product])
)
RETURN
    _Rows >= 1

which gets you a True/False column

[vgeldbr]

Thanks. This works perfectly. I'm not sure if I can mark both your @PaulOlding  solution and @ValtteriN solution as acceptable. Going to try!

[ValtteriN]

@vgeldbr 

I am not sure if you got the calculation group method to work. The key factor in using that approach is to filter a dimension table in the calculation group instead of a fact table. 

[vgeldbr]

@ValtteriN I've not been able to get it to work. See my post below in response to your suggestion. 

[ValtteriN · Accepted solution]

Hi,

For this kind of filtering I would create a filter measure and apply it to your visual.
So e.g. here we only keep rows where our item's material is Wood

Filter when item =

var _item = MAX(ItemExample[Item]) return

IF(COUNTROWS(FILTER(all('Matrix example'),'Matrix example'[Item]=_item&&'Matrix example'[Material]="Wood"))>0,1,0)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[vgeldbr]

I'm not quite getting the logic here. The code based on your example:

VAR _item =
    MAX( 'ITRDB Daily_ProjectFinancial'[ProductServiceProject] )
VAR Result =
    IF(
        COUNTROWS(
            FILTER(
                ALL( 'ITRDB Daily_ProjectFinancial' ),
                'ITRDB Daily_ProjectFinancial'[ProductServiceProject] = _item
                    && 'ITRDB Daily_ProjectFinancial'[LeadershipRptInvestGroup] = "MFS Production Cloud"
            )
        )
            > 0,
        1,
        0
    )
    
 RETURN
     
   Result

And the result I see:

As you see, the first and the last project get a 0 when I expect them all to show 1 because one of the lines has MFS Production Cloud as the value.

[vgeldbr]

OK, found the issue but not the solution. I have a filter on the page for another column in the table (which is required). When I remove that filter it works. I guess I need to find a way to have the measure ignore the impact of that additonal filter on a column but no combination is working yet.

[vgeldbr]

I identified a solution. I removed the zero returned on false in the IF statement. This ensures that a line is not shown in the visual for projects that are otherwise filtered out from external slicers.

VAR _item =
    MAX( 'ITRDB Daily_ProjectFinancial'[ProductServiceProject] )
VAR Result =
    IF(
        COUNTROWS(
            FILTER(
                ALL( 'ITRDB Daily_ProjectFinancial' ),
                'ITRDB Daily_ProjectFinancial'[ProductServiceProject] = _item
                    && 'ITRDB Daily_ProjectFinancial'[LeadershipRptInvestGroup] = "MFS Production Cloud"
            )
        )
            > 0,
        1
// Remove this line -- ,
// Remove this line --       0
    )
    
 RETURN
     
   Result

[vgeldbr]

Thanks @ValtteriN , I will give this a try. The reason I did not pursue (and still have reservations about using a measure) is that any filtering then has to be applied to every visual on the page. 

[ValtteriN]

@vgeldbr 

Ah, this is a common issue. However, I have workaround for this. You can create a calculation group using visual studio or tabular editor and apply the logic there. 

e.g.

CALCULATE(
SELECTEDMEASURE(),
FILTER('Table',[Filter measure]=1))


Then you can apply this logic on page level by selecting the calculation group.

[vgeldbr]

@ValtteriN  I'm intersted in pursuing your idea about using a calculation group to use a measure as a page level filter. I've implemented this but it does not work. Any thoughts on what I might be missing?

VAR _item =
    MAX( 'ITRDB Daily_Projects'[ProductServiceProject] )
VAR Result =
    IF(
        COUNTROWS(
            FILTER(
                ALL( 'ITRDB Daily_Projects' ),
                'ITRDB Daily_Projects'[ProductServiceProject] = _item
                    && 'ITRDB Daily_Projects'[LeadershipRptInvestGroup] = "MFS Production Cloud"
            )
        )
            > 0,
        1,
        0
    )
RETURN
    Result

[ValtteriN]

@vgeldbr 

I might know where the issue is.  This has to with your table interactions. When using the logic with calculation groups all of the columns/measures used in visualisations have to be affected by the group. E.g. Here I have a table with two rows: employee name and a MAX measure of that name:

When applying my filter via calculation group rows with "John" will disappear:

Filter Measure = IF(countrows(FIlter('Transaction Table2','Transaction Table2'[Employee]<>"John"))>0,1,0)

CALCULATE(SELECTEDMEASURE(),FILTER('Transaction Table2',[Filter Measure]=1))

However if I add measures that disregard this or if I only have columns in my visualization there is no effect:

To get this logic to work the filter measure can't be displayed and every measure needs to be affected by the FILTER in the calcuation group. The goal here is to return BLANK values based on the group logic.