Creating a table with multiple values filtered from same table

Question

I have a table where the Loan Number for multiple sources reside on the same table.&nbsp; The field which differentiates it is the Type Column.&nbsp;I already created a column for each loan number (Owner Loan and Product Loan).Issue I have is that when I attempt to addit to a table they insist on creating seperate rows.&nbsp; I would like for it to be on one row with a single order number which it applies to.&nbsp;Any suggestions?Example.Table A&nbsp;Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TypeOrder Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23456&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9

tm_evan · Answer

Desired Result&nbsp;Order Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Owner Loan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Product LoanOrder Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3456

anonymous · Answer

Not super sure if this will work in your particular scenario, but the "Pivot Column" button under Edit Queries does exactly what ur asking.

tm_evan · Answer

Thank you Scottsen for replying to this post. I am still relatively new to Power BI and the suggestion you made is something I have never even considered. I did try it today with no success unfortunately with error messages stating my data is nested.

The solution I am going for is more along the lines of DAX expressions. I am also realizing that I will need to do calculated columns in a table where I only have 1 unique root id. I unfortunately keep getting errors - ' A table of multiple values was supplied where a single value was expected.'

Funny thing is if I look at the raw data of table B (Where the loan data is, for the one unique root ID we have 3 rows for each of the 3 loan numbers suppled. My 1 column is only providing the logic to return 1 of them.

Here is the DAX statement used.

OwnerLoan = CALCULATE(VALUES('pfm B'[Number]),Filter('B','B'[Type] = 2 && 'A'[RootId#] = 'B'[RootId#]))

patina · Answer

Yes Evan the Pivot Column option would serve you well if you were wanting to create the columns within your original table. You would use Type as your pivot column and Number as Values.&nbsp;If you want the information in an additional table, that expression should work.&nbsp;You might also try 'summarizing' your table under the Modeling tab. You would create a New Table and the expression would be&nbsp; = SUMMARIZE('Name of your Table', 'Name of your Table'[RootID#]). Then create the 3 columns bringing in the Number by filtering by Type and RootID.

tm_evan · Answer

Hi Parita, You have been so instrumental in helping me before, I am glad you replied to me again.&nbsp;I have attempted to do the Pivot Column again and I keep getting the error - 'Expression.Error:&nbsp; We cannot apply a pivot operation over a table that contains columns with nested data.&nbsp; Please remove those columns first.'&nbsp;Problem I have is that I do not even see any nested data.&nbsp;I also attempted the Modeling table solution to create a new table.&nbsp; Tat too does not work since It appears I am not getting the syntax correct to include filtering.&nbsp;&nbsp;Any suggestions?

Forum Discussion

Creating a table with multiple values filtered from same table

9 Replies

Related content

Creating multiple Audiences in PowerBI App

Multiple Years Filter

DAX FILTER with multiple criteria

Multiple messages when new item created

create measure with multiple filters