Forum Discussion

ghaines's avatar
ghaines
Resolver I
3 years ago
Solved

Using same column in rows and columns of matrix visual: Detecting row vs column context

I have different bands of customers defined based on their average monthly sales.  I'd like to display a matrix of migration between the bands i.e. how many customers have moved from band x to band y...
DAX
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    ghaines No, DAX is pretty clueless about stuff like that. There is ISFILTERED and ISCROSSFILTERED but that's about it.

ghaines's avatar
ghaines
Resolver I
2 years ago

My matrix was made with two disconnected tables that were identical except for name, and were used for the row and column axes of the matrix.  I could then use the values in row and column to define the logic of the dax expression that created the return value.  I don't think it's possible to read two different table contexts from row and column contexts.

tpaliwal's avatar
tpaliwal
Frequent Visitor
2 years ago

Hi Ghaines,

Is it possible for you to share the PBIX file so that I can see the logic and use of DAX?

 

Appreciate your help!

  • ghaines's avatar
    ghaines
    Resolver I
    2 years ago

    No, sorry.  The dax expressions and disconnected tables contain strata of unit sales/month for different customers that would give some indication of our financials, and there is identifying information throughout.

    • tpaliwal's avatar
      tpaliwal
      Frequent Visitor
      2 years ago

      Totally understand, no worries.

       

      If there is a way for you to show the DAX by changing the names that will be helpful.

      If not possible then that's okay.

      Thank you!

       

      • ghaines's avatar
        ghaines
        Resolver I
        2 years ago

         

        BandsVariablePeriodUnitsDoubleFiltered = 
VAR LowerLimitPrev = MIN('_Customer Bands by Unit Sales Alt (Columns)'[GTOET])
VAR UpperLimitPrev = MAX('_Customer Bands by Unit Sales Alt (Columns)'[LT])

VAR LowerLimitCurr = MIN('_Customer Bands by Unit Sales'[GTOET])
VAR UpperLimitCurr = MAX('_Customer Bands by Unit Sales'[LT])
VAR CustomerFilter = FILTER(VALUES(Customers[Customer Name]),
        [_BandsUnitsCYPeriod]> 0 && [_BandsUnitsCYPeriod] >= LowerLimitCurr && [_BandsUnitsCYPeriod] < UpperLimitCurr &&
        [_BandsUnitsVariableComparisonPeriod] > 0 &&
        [_BandsUnitsVariableComparisonPeriod] >= LowerLimitPrev && 
        [_BandsUnitsVariableComparisonPeriod] < UpperLimitPrev
)

    


RETURN 
CALCULATE(COUNTROWS(DISTINCT(VALUES(Customers[Customer Name]))),
    CustomerFilter)

         

         

        I wrote it a while ago, it's a bit messy.  The "Customer Bands by Unit Sales" type tables are the disconnected tables, with bands defined by sales less than (LT) a value, and greater than or equal to (GTOET) a value.  I then filter the customer list based on where it falls in the context.  Since I am looking for migration between bands and the bands do not include 0 sales, I filter those out too.  Lost and gained customers is a separate visual.

         

        I think the distinct method in the return is superfluous but I'm not going to poke the bear.  The reason I evaluate VALUES([Customer Name]) is because our data is rife with duplicates and some have the same name directly because of that.