filter columns and rows of a matrix

[cris007]

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

[v-yangliu-msft · Accepted solution]

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

[OwenAuger]

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