topic Re: Table matrix custom representation - using any possible DAX queries in DAX Commands and Tips

Table matrix custom representation - using any possible DAX queries

deb_power123 — Fri, 05 Nov 2021 12:49:08 GMT

Hi All,

I have the below source table as my input which is a table matrix with a set of 5 coulmns namely :- Name ,city, school,Age,type etc.

Input source table:

Name	city	school	Age	type
A	London	ABC	12	Permanent
B	Berlin	ADE	10	Temporary

Output expected:

Name	city
A	London
school	ABC
Age	12
type	Permanent
Name	city
B	Berlin
school	ADE
Age	10
type	Temporary

Is it possible to get the expected output in the given format above .Each row should breakdown into row and column[contianing the row values].Is it possible through any DAX assuming the row headers and column headers wont change anytime in future and no new enteries will be added.

Kind regards

Sameer

Re: Table matrix custom representation - using any possible DAX queries

VahidDM — Fri, 05 Nov 2021 22:53:43 GMT

Hi deb_power123

Try this code to add a new table:

Table 2 = Var _A = filter('Table','Table'[Name]="A") Var _B = filter('Table','Table'[Name]="B") return UNION( row("Name",MAXX(_A,[Name]),"City",MAXX(_A,[city])), row("Name","School","City",MAXX(_A,[school])), row("Name","Age","City",MAXX(_A,[Age])), row("Name","Type","City",MAXX(_A,[type])), row("Name",MAXX(_B,[Name]),"City",MAXX(_B,[city])), row("Name","School","City",MAXX(_B,[school])), row("Name","Age","City",MAXX(_B,[Age])), row("Name","Type","City",MAXX(_B,[type])) )

Output:

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

Appreciate your Kudos!!

Re: Table matrix custom representation - using any possible DAX queries

deb_power123 — Mon, 08 Nov 2021 08:48:11 GMT

Hi VahidDM

Two things I observed below :-

1) Can we show this in the table visual , the same way you showed in tranformation tab.I need the same representation in table visual in PowerBI?

2) You have represented the above calculation with two variables A and B , what if there are 100 such enteries . I beleive we cannot define 100 variables or even more to handle it in that case so can we use some possible iterations to handle it?

If so ,then could you please let me know in your above apporach how to make the changes incase there are 100 entries which we need to show in the same format you have shown for two enteries.? Please suggest.

I will wait for your response on this..

Thankyou very much for the first approach but it will be great if we can know how to handle more number of entires?say 100 or may be more.

Kind regards

Sameer

Re: Table matrix custom representation - using any possible DAX queries

VahidDM — Mon, 08 Nov 2021 11:12:21 GMT

Hi deb_power123

1) You can use new table's columns to create a table visual in Power BI.

2) it's hard to solve that with DAX, can you use Power Query?

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

Appreciate your Kudos!!

Re: Table matrix custom representation - using any possible DAX queries

deb_power123 — Mon, 08 Nov 2021 12:06:09 GMT

Hi VahidDM

Yes powerquery is also fine.Can you please demonstrate me how to do it using the above input source.I am kind of struggling to conclude this

Kind regards

Sameer