The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I am using direct query mode. Kindly help me to build dax to get desired output table.
Thanks
Sample Data
Account Number | Line Number | Address |
1001 | 1 | AAAAAAAA |
1001 | 2 | BBBBBB |
1001 | 3 | CCCCCC |
1001 | 4 | DDDDDD |
1001 | 5 | EEEEEE |
Desired O/p
Account | Address 1 | Address 2 | Address 3 | Address 4 | Address 5 |
1001 | AAAAAAAA | BBBBBB | CCCCCC | DDDDDD | EEEEEE |
Hi @Anonymous ,
By my tests with Direct Query, you may could try unpivot in query editor. Please refer to the M Query.
let Source = Sql.Database("xxxxxx", "cherry"), dbo_Table_8 = Source{[Schema="dbo",Item="Table_8"]}[Data], #"Changed Type" = Table.TransformColumnTypes(dbo_Table_8,{{"Line Number", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account Number", "Line Number"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Attribute]&""&[Line Number]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Line Number", "Attribute"}) in #"Removed Columns"
Here is the output.
Best Regards,
Cherry
Plz consider the below screenshot
as per the screenshot u have provided where custom is exp_line_number, address is explantion, account mnumber is transaction_sys_no .
Unable to re-create the same M-code on my file
unable to recreate the same in my application .
From where u got line number field
@Anonymous If the source is a database, I suggest to write a SQL Query using UNPIVOT while retrieving the data itself in Direct Query mode. Let me know if you are happy with that approach.
Proud to be a PBI Community Champion
i have written a dax query which returns values for the dimension wise .. if there is no values for any particular dimension then its not showning the dimension also . so , i have written if considition on the measure using isblank but i m getting error while using this if condition . could you help how to fix it .
If dont mind can i have ur mobile number if u r from india .
Dimension:
Account number, trascation number
Measure :
EXPLANATION1_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=1)
EXPLANATION2_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=2)
the above measures might have null values for those dimensions . I want replace those null values with "N/A".
Thanks,
Thanks for respnse ..
we are strictly advice not to write any sql stmt . so , looking forwards for any dax query help .
Thanks
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
103 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
86 |