topic Using same column in rows and columns of matrix visual: Detecting row vs column context in DAX Commands and Tips

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

ghaines — Fri, 18 Nov 2022 02:02:18 GMT

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

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.

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

Greg_Deckler — Fri, 18 Nov 2022 02:19:23 GMT

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

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

ghaines — Fri, 18 Nov 2022 02:24:33 GMT

Thought so, thanks!

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

tpaliwal — Thu, 22 Feb 2024 00:08:28 GMT

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.

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

ghaines — Thu, 22 Feb 2024 00:14:49 GMT

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.

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

tpaliwal — Thu, 22 Feb 2024 00:19:11 GMT

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!

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

ghaines — Thu, 22 Feb 2024 00:23:22 GMT

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.

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

tpaliwal — Thu, 22 Feb 2024 00:27:21 GMT

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!

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

ghaines — Thu, 22 Feb 2024 00:39:04 GMT

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.