topic Re: filter columns and rows of a matrix in DAX Commands and Tips

filter columns and rows of a matrix

cris007 — Tue, 04 Jul 2023 09:33:20 GMT

Hi all,

Let's say I have the below dataset :

Brand	Value	Competitor
Audi	5000	No
Skoda	4000	No
VW	6000	Yes
BMW	4000	Yes
Peugeot	3000	Yes
Tesla	5000	Yes
BYD	4000	Yes
Citroen	1000	Yes

I want to create a Matrix for which in colomns I want to put all the brands that are not competitor and in rows, all the brands that are competitors.

So it would look as below :

	Audi	Skoda
VW
BMW
Peugeot
Tesla
BYD
Citroen

Anyone knows how I can create a dax to filter my rows and columns?

Thank you

Re: filter columns and rows of a matrix

OwenAuger — Tue, 04 Jul 2023 11:27:04 GMT

Hi cris007

To be able to give a proper answer, my first question is: what values would you want to appear in the body of the matrix? Can you give an example?

Regardless, I would suggest that you need a secondary copy of a Brand dimension (possibly with inactive relationship) in order to make this work.

Regards

Re: filter columns and rows of a matrix

Anonymous — Thu, 06 Jul 2023 04:26:25 GMT

Hi cris007 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 = var _table= SUMMARIZE('Table','Table'[Brand],'Table'[Competitor]) var _tableno= FILTER(_table,[Competitor]="No") var _tablenotrue= SUMMARIZE(_tableno,[Brand]) var _tablenotrue1= ADDCOLUMNS( _tablenotrue,"Brandgroup",[Brand]) var _tablenotrue2= SUMMARIZE( _tablenotrue1,[Brandgroup]) var _tableyes= FILTER(_table,[Competitor]="Yes") var _tableyestrue= SUMMARIZE(_tableyes,[Brand]) return CROSSJOIN( _tablenotrue2,_tableyestrue)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly