How to Create a sumarize table in Power Bi with two columns in the X matrix. Dax Code.

[felipereis50]

I have a simple matrix table, created inside the Power Bi View and this view has 500.000 rows .  I put information in the Column field.
I want to know, how can I create the same results of the matrix, but, writing the code by myself?

In the table below, I'm showing the table and the result from a Pivot table , in a matrix view by column. 
What I trying to say is> How to create a matrix view without using the matrix view tab from Power Bi? 

I think I have to use sumarizecolumns by, But i don't know how to sumarize by column field.

A TABLE

Invoice	Account	Amount
15	33001	1,00
16	33001	1,00
01	33001	1,00
02	33002	1,00
03	33003	1,00
03	33001	1,00
03	33001	1,00
04	33001	1,00
04	33001	1,00
04	33001	1,00
05	33002	1,00
05	33002	1,00
05	33002	1,00
06	33003	1,00
06	33003	1,00
06	33003	1,00
07	33001	1,00
08	33001	1,00
09	33002	1,00
10	33003	1,00

The PIVOT TABLE (the same why we use in excel Pivot) is the result that I want to know to write the code and create by myself..

Invoice	33001	33002	33003
01	1
02		1
03	2		1
04	3
05		3
06			3
07	1
08	1
09		1
10			1
15	1
16	1

 
PS: I don't know how to Attach print screen here so I put the table by hand.

[mahoneypat]

I agree this should be done in the query editor.  However, you could do it with multiple SELECTCOLUMNS or probably with GENERATE/ROW.  However, not knowing your ultimate goal to get pivoted data out, but it may be easier to just put your unpivoted query in Excel, load the table, make a pivot table, and then copy the pivoted data out of there.  Or you could do it with Power BI Report Builder too with a matrix visual thatn can be exported as such.

Regards,

Pat

[felipereis50]

Hello  @mahoneypat , 

Yes, your Suggestion it is exactly what I done in first place.
I did it directly through excel and then I copied (copy and past) to a new excel the result I did on the pivot table.
The reason is because I need to export this result for the audit and it needs to be in exactly that form in CSV.

But, when I copied to the new excel, it is taken to long. (because it has 500.000 rows)

After that, I went to Dax Studio>Dax Builder, and try the same thing, but Dax Builder has no option to create a Pivot Table by columns. 

I even sent a message to Alberto Ferrari (in the comments of a video) to see if this function exists, or even to update it in the future.

After all that, I thought: How could this result be created in Dax? So I can copy the code inside DaxStudio and export it in csv more fast.
Because, in Dax Studio, exporting large data is very fast.

I imagine that inside the matrix function inside Power Bi, there is a rule for creating the Matrix visualization , so I imagine that it is possible to create it manually.

When I create a matrix view inside Power Bi, I go to: view> Performance Analyzer> and then I copy the query (of the matrix visualization result of performance analyzer)

Because inside the query copied, there is all the code created to set the matrix. But, as it is a matrix view, when I place the code it inside Dax Studio, two sheets are created. One containing a table, and the other containing the column data that I had used in the matrix visualization.
As I am not very good at Dax, I thought about finding out if someone has the knowledge and I learn. I got curious.
Unfortunately I don't know how to paste an image here to show you mor easily,

Regards,

[parry2k]

@felipereis50, as suggested by @mahoneypat , in this case, either I will use Power BI Report builder (paginated reports) or the easiest, is to use "Analyze in Excel", connect to  Power BI dataset, and then pivot the data the way you want.

[mahoneypat · Accepted solution]

Like other visuals, the matrix is just a visualization of a table behind the scenes (as you saw when you copied it to DAX Studio).  I think the easiest way to get your result is to use Power BI Report Builder.  You can use the same query from your visual that you copied into DAX studio to create the dataset.  You can then make a matrix and export it as such (and it should be fast).

Power BI Paginated Reports: Visualizing Data - YouTube

Regards,

Pat

[felipereis50]

Thank you for the suport guys.
I'll try that.
Infact, this is the answer from Alberto Ferrari about my case:

SQLBI

All the visuals in Power BI (Matrix included) get the data in a table with one row for each "cell" at the visual granularity. Only if you have measures in the column the table is as you see it in the matrix. When you have a an attribute in the columns (for example, Product[Color]), then the matrix visual reorganizes several rows of the resulting table in multiple columns of the same row in the Matrix. You don't have a DAX code to get the same result as you see in the Matrix. If you have that requirement, create the query in MDX - capture the MDX query created by Excel querying the Power BI model (use Analyze in Excel for Power BI Desktop) and the use that MDX code, it's the simpler way to do that.

DAX Tools - DAX Studio 7 – Query builder - YouTube"

[parry2k]

@felipereis50 in PQ, start a new blank query, click advanced editor, and paste the following code and you will see the pivoted output.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRV0lEyNjYwMATShkqxOkAhMwwhMBNNyAgqZIQQMoYKGWMKGeIVMiFXyBTTEcQJmWE6lTghc0xHWGAKWWLYaGiAalYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Account = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"Account", Int64.Type}, {"Amount", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Account", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Account", type text}}, "en-CA")[Account]), "Account", "Amount", List.Sum)
in
    #"Pivoted Column"

[parry2k]

@felipereis50 better to pivot it in PQ and then you can use a simple visual.

[felipereis50]

Hi Parry, I'm sorry. I didn't want to explain my request  before, to not complicate the contentt. I'd like the code in Dax (Not in PQ). I would like to mount the result within Power Bi as a table and copy the code to Dax Studio.