Forum Discussion

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
4 years ago
Solved

Calculated Column across row context

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...
DAX
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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's avatar
    PaulOlding
    4 years ago

    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's avatar
vgeldbr
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
vgeldbr
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
    vgeldbr
    Icon for Helper IV rankHelper IV
    4 years ago

    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