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.

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.