Iterate over a dynamic table

[Silvard]

I have a fields parameter table containing various dimensions from many different tables.

How can these dimensions dynamically be used in an iterator like MINX, based on slicer selection?

 MINX, Calculatetable,Addcolumns, Summarize all require a table and do not support using switch/if to dynamically choose the table/column as far as I can tell.

[Anonymous]

Hello,Shravan133 and Fowmy ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Silvard .I am glad to help you.

Using field parameters is indeed a good option. I noticed that you are trying to dynamically change the output of the form using a field parameter +  IF() judgment function.

You need to be careful. Directly created calculated tables and columns are static, they are not able to accept external filters.
The only thing that can dynamically change based on the calculation environment is measure.
I noticed that you tried to create dynamically changing virtual tables in the measure using the addcolumns function. This is a correct approach.
But you need to change the final approach based on your final expected results.
1. Tables that return results dynamically: If you would like to see a dynamically generated table that contains the results of calculations based on the different dimensions and measures selected by the slicer.
This is currently not possible with power bi. Your alternative is to create multiple measures with the same filtering environment, place them in a table visual, and stitch them together into a dynamic table.
URL:
Table Filter does not work based on slicer selecti... - Microsoft Fabric Community

Use report readers to change visuals (preview) - Power BI | Microsoft Learn

2. dynamically return the result of the MEASURE calculated using the virtual table created with addcolumns (some scalar value, not a table with multiple rows and columns).
You could continue your attempt.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Silvard]

Hi @Anonymous 

@Thanks for your detailed answer.

I'm a big believer in adding flexibility and creating dashboards that can be tailored by the user to suit their requirements. This is why I love the idea of using parameter fields. It seems like there is a greater level of flexibility when it comes to measures, where you can use switch statements for selection due to being a scalar value and not table. I suppose when you use UNION you're creating a disconnected table as you point out and I'm sure you could find a way to connect this table within the measure, maybe by using TREATAS?

But when you're dealing with 35 columnsmany from different tables and various measures this doesn't seem to be feasible.

I'm trying to create a normalisation measure for conditional formatting, using minx and maxx. My current measure has over 1000 lines of dax and while it's got a reasonable execution time, it's laggy when trying to update the measure.

The problem I have is that I would like for users to be able to select the column to use within a matrix (essentially 3 parameters, 1 for row, 1 for column and 1 for measure, and if I have to incorporate all these columns, rows and measures, I will need to use thousands of lines of data using my current structure ( lots of isinscope and switch for measures defined within each minx and maxx.

Have you got any other alternative I can use?

Your continued help is much appreciated.

[Anonymous]

Hi,@Silvard .Thank you for your reply.
Understand your concerns. Power BI has the flexibility to change the output of multiple measures to approximate the effect of dynamically displaying a table, which is inherently complex
Perhaps you could consider calculation groups, which could help you reduce repetitive DAX code.(Unfortunately, I'm not very good with calculation groups, but in terms of ultimately realizing your needs, it's more difficult to do so).

[Silvard]

Hi @Anonymous 

thanks for your prompt reply. 

I'm in fact already using a calculation group to override the conditional formatting measure in the matrix and in order to do that I use isselectedmeasure. Because I have to call out the measure to override it, I can't also use selectedmeasure to select the other measures, which means I have to call out all measures in  every iterator with a switch statement.

[Fowmy]

@Silvard 

I suggest you go with a SWITCH logic:

Parameter Table:

Parameter = {
    ("Segment", NAMEOF('financials'[Segment]), 0),
    ("Product", NAMEOF('financials'[Product]), 1)    
}

Measure, to get the Minimum cost across selected field from the parameter.

Min_FP = 
SWITCH(
    SELECTEDVALUE(Parameter[Parameter Order]),
    0, MINX( VALUES( financials[Segment] ) , [Cost] ),
    1, MINX( VALUES( financials[Product] ) , [Cost] )
)

[Silvard]

Hi @Fowmy 

I greatly appreciate the suggestion.

This would be similar to using switch and isinscope followed by minx.

I'm trying to create conditional formatting and need to normalise the value. This requires both MinX and MaxX. Using your suggested method creates many lines in the dax ( I have over 30 dimensions) and I'm looking for a simplified way, ideally where I can input a variable in the table within MinX and MaxX.

Adding to the complexity is that I also have various measures that I would like to incorporate. 

I have however found a way to add these in dynamically in the iterators by using Addcolumns(table,"MeasureValue",Switch(MeasureSelected,"Measure1",[Measure1] and so on. I can then refer to MeasureValue within the iterator.

I somehow need similar for the table.

[Shravan133]

Maybe use the SELCTEDVALUE function?

For example:

DynamicMinMeasure =
VAR SelectedField = SELECTEDVALUE('Fields Parameter'[FieldName])

VAR DynamicTable =
SWITCH(
TRUE(),
SelectedField = "CustomerName", 'CustomerTable', -- Table for CustomerName
SelectedField = "ProductCategory", 'ProductTable', -- Table for ProductCategory
SelectedField = "SalesRegion", 'RegionTable', -- Table for SalesRegion
SelectedField = "OrderDate", 'OrderTable', -- Table for OrderDate
'DefaultTable' -- Default table if none selected
)

VAR DynamicColumn =
SWITCH(
TRUE(),
SelectedField = "CustomerName", 'CustomerTable'[CustomerName],
SelectedField = "ProductCategory", 'ProductTable'[ProductCategory],
SelectedField = "SalesRegion", 'RegionTable'[SalesRegion],
SelectedField = "OrderDate", 'OrderTable'[OrderDate],
BLANK() -- Default case
)

RETURN
MINX(DynamicTable, DynamicColumn)

[Silvard]

Thanks for your input @Shravan133 

Unfortunately, this won't work:

1. You can't use selectedvalue with fields parameters. (See https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/)

2. You don't have the option of using a switch variable inside a function that requires a table like MinX.