cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andreyinfo
Frequent Visitor

Help needed - How to create a single row table with the value from a selectedvalue.

I need to create a table and then with this table, to create another table. But the first table need to be created from a selection.

Tried everything and I just can't go on.
The measure getting the selectedvalue:

 

 

Selected = SELECTEDVALUE(dProducts[ProductCode])

 

 

The first table attempt :

 

 

FilteredBySelectedValue = 
Var _ProductCode = [Selected]
Return
ROW( "ProductCode",_ProductCode)

 

 

The second table attempt :

 

 

fBasketRelated = 
FILTER(
CROSSJOIN(
VALUES(FilteredBySelectedValue[ProductCode]),SELECTCOLUMNS( VALUES( fSalesDetails[ProductCode]),"RProductCode",[ProductCode])),[ProductCode]>[RProductCode])

 

 


The result : Nowhere.

f1f1

The model :

f2f2
@stevedep 


 The PBI file 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

That's not possible. Tables in a model are always static and loaded at load time (then set in stone!).

Secondly, you can only create table variables in dynamic code that describes measures but such tables are gone as soon as the measure's been calculated.

Best
D

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

I think it depends what you expect to do with the result.  If you start constructing a table from a selectedvalue in a filter context then you can use that table in memory and transform it as needed but the final return value needs to be scalar (a count of rows, or a CONCATENATEX of values).

So, if got it right, my final result needs to be a measure, because a table return more than one result.

If so, there is no solution for my needs?

Because I need to create a table : 

 

Table = 
FILTER(
    CROSSJOIN(
        VALUES(FilteredBySelectedValue[ProductCode]),SELECTCOLUMNS( VALUES( fSalesDetails[ProductCode]),"RProductCode",[ProductCode])),[ProductCode]>[RProductCode])

 


 And the first values ("VALUES(FilteredBySelectedValue[ProductCode])") needs to come from a selectedvalue.

 

your final result type must match the type of the original value.  

 

How do you expect to consume the table that you created out of  a scalar value?

I thought that with this code I would create a table with a single row and column(Figure 1), and it seems a scalar result because it is only one of it's kind. Please, note that if I set the value manually (VAR _ProductCodeTyped ) I get the desired value and I can consume the table to generate the second table (figure 2).

Figure 1Figure 1

And then with the second table I would create columns and measures.

 

Figure 2Figure 2

If I got your question right. @lbendlin .

Anonymous
Not applicable

That's not possible. Tables in a model are always static and loaded at load time (then set in stone!).

Secondly, you can only create table variables in dynamic code that describes measures but such tables are gone as soon as the measure's been calculated.

Best
D

you might get away with a single row/single column table as the final output. But not as an intermediate step to a larger table.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors