Forum Discussion

deb_power123's avatar
deb_power123
Helper V
4 years ago

Table matrix custom representation - using any possible DAX queries

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:

NamecityschoolAgetype
ALondonABC12Permanent
BBerlinADE10Temporary

 

Output expected:

Namecity
ALondon
schoolABC
Age12
typePermanent
Namecity
BBerlin
schoolADE
Age10
typeTemporary

 

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

DAX
DAX Calculation
DAX Measures
Table Column
Table Visual

4 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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!!

     

    • deb_power123's avatar
      deb_power123
      Helper V
      4 years ago

      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

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        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!!