topic How to get values from a row and transpose it to columns. in DAX Commands and Tips

How to get values from a row and transpose it to columns.

Krish4Work — Mon, 12 Dec 2022 05:47:33 GMT

Hi.. I am pretty new to Power BI

I have two tables, one For the transaction Document and another for the Tender details of the transaction.

In the tender tables, for every tender it will create a row with the tender type (eg: Cash, Master Card, Visa etc) and the tender amount.

Even if a single transaction has multiple tenders (Split tenders) like cash and one of the cards, it has multiple record of rows for each tender type.

The tender table contains Transaction SID which is the reference to the Transaction Document.

My requirement is to Create coloumns according to the multiple tender type(Cash, mastercard, visa) which will contain the tender amount as values and group it according to the transaction SID.

I tried adding a custom coloumn with

Cash = LOOKUPVALUE(TENDER[AMOUNT],TENDER[DOC_SID],DOCUMENT[SID],TENDER[TENDER_TYPE],0)

but it gives an error

A table of multiple values was supplied where a single value was expected.

Please help me out in this.

Thanks in advance.

Re: How to get values from a row and transpose it to columns.

tamerj1 — Mon, 12 Dec 2022 07:32:22 GMT

Hi Krish4Work
Do you have a relationship between the two tables? Can you please provide sample data from both tables along with the expected result?

Re: How to get values from a row and transpose it to columns.

Krish4Work — Mon, 12 Dec 2022 08:36:29 GMT

Hi tamerj1

Thanks for replying!

Please find the .pbix file in this attached wetransfer link.

https://we.tl/t-eZ5i7pp6ty

Both the tables have relatioship ie Tender[Doc_sid] = Document[sid].

Re: How to get values from a row and transpose it to columns.

tamerj1 — Mon, 12 Dec 2022 09:58:02 GMT

Hi Krish4Work
Please refer to attached sample file. Hope this is what you're looking for

Cash = SUMX ( FILTER ( RELATEDTABLE ( TENDER ), TENDER[TENDER_TYPE] = 0 ), TENDER[AMOUNT] )

Re: How to get values from a row and transpose it to columns.

Krish4Work — Mon, 12 Dec 2022 11:08:27 GMT

Thankyou tamerj1 you've been a great help.

Your solution is working for me.