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

     

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity 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
Icon for Helper IV rankHelper 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
    Icon for Community Champion rankCommunity 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
      Icon for Helper IV rankHelper IV
      4 years ago

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

        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.