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

[ghaines]

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:

Customer band now                     1              2              3              4             5                6

Customer band 3 months ago

1

2

3

4

5

6

The values in the columns and rows will of course be identical.  I'm pretty sure you just can't have the same table column on the row and column of the matrix visual.  My solution is to duplicate the values in another table, providing the structure for this table. 

However, my actual question is around detecting the filter context:  I have used SELECTEVALUE, ISINSCOPE, ISFILTERED previously.  Within DAX, is there a way to detect whether these filters originate from the row context, column context or from a filter applied to the visual.  Also, is there a way to detect whether the filter is at the visual, page or report level?  I imagine you could find some use cases for that kind of information.

[tpaliwal]

Hi,

I'm trying to create a similar matrix as I have produced through sample dataset.

I have different Lifecycles and I'm trying to show the migration of customers through different lifecycle over time.

My question is around how do I use same field in rows and columns. And also make it dynamic based on the selected time period.

[ghaines]

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]

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]

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]

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]

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.

[Greg_Deckler · Accepted solution]

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

[ghaines]

Thought so, thanks!