Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!!
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
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!!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |