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

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.