Forum Discussion

vgeldbr's avatar
vgeldbr
Helper 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 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.

 

 

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

     

12 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    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!


     

    • vgeldbr's avatar
      vgeldbr
      Helper IV
      4 years ago

      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's avatar
        ValtteriN
        Community Champion
        4 years ago

        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's avatar
      vgeldbr
      Helper 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
        Helper 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.

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    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
      Helper IV
      4 years ago

      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's avatar
        ValtteriN
        Community Champion
        4 years ago

        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.