Calculated Tables and Filed Parameters

[ARPower_Beast]

Can a calculated table access a field parameter table, specifically the “Parameter Order” column?  I am try to use SELECTEDVALUE(Parameter[Parameter Order]) to filter on the current parameter that is selected with no luck.  Here is a simplified version of what I am doing.  I’m trying to store the last value selected from a slicer that displays a list of "Field Paramaters":

LastValueTest1 = {( 1, ParMETRICS[Order_Value] )}

Order_Value is a measure that returns the currently selected order number of the currently selected field parameter.  It works when added to a “Card” visualization but returns blank in a calculated table.

Same for this calculated table:

LastValueTest2 = {( 1, SELECTEDVALUE(ParMETRICS[METRICS Order] ,4))}

It always defaults to the default value of 4 which means SELECTVALUE is always failing to find a single value.

It looks like this is not possible.  Does there need to be some sort of context between my calculated table and my field parameters?

Here is a breakdown of the final dynamic table.  It provides granular details to a higher level matrix visualization.  The virtual table filtering works with hard coded numbers but it’s “a no go” when I try to access the currently selected field parameter.  I am missing a link between my calculated table and my field parameter table somewhere. 

As a last effort I am trying to somehow store the selected field parameter in a table after failing to get SELECTEDVALUE to work inside the calculated table.

DynamicTable =

var MetricSelected = SELECTEDVALUE( ParMETRICS[METRICS Order],4 )  //This line is my issue, SELECTVALUE should return the currently seleted paramater order number, but does not, it defaults to 4

var VirtualTable1 =
                CALCULATETABLE(
                                SELECTCOLUMNS(table1,field1,field2),
                                filter1)   ---first filter
var VirtualTable2 =
                CALCULATETABLE(

                                SELECTCOLUMNS(table1,field1,field2),
                                Filter2)   --second filter

Var CombinedTable =
                UNION(
                                SELECTCOLUMNS(VirtualTable1, field1,field2,
                                “Condition”,0          --added column to label the row for filtering

                     ),
                                SELECTCOLUMNS(VirtualTable2 ,field1,field2,
                                “Condition”,1          --added column to label the row for filtering
                              )
                    )
RETURN
FILTER(CombinedTable,[Condition] = MetricSelected)

It woudl be really dynamic if I could get MetricSelected to store the currently selected paramater.

[ARPower_Beast]

Hello @Anonymous 

Thanks for directing me to that post.  It was very helpfull.  It also made me realize that dynamic tables are not possible because caculated tables are static.  And measure can not return a table.  Unless I make a measure for ever column?  It does not seam reasonable, but I will keep at it for a little longer.  At least I have turned around from a dead end.

Thanks

[Anonymous]

Hi @ARPower_Beast 

Thank you for your quick reply.If my reply help you, you can consider to accept it as a solution so that more user can refer to.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @ARPower_Beast 

If you want to use the dynamic table, the better way is that you use the measure to create the virtual table, the calculation table is static. It can only return the corresponding results based on the values ​​selected by your current slicer. No matter how your slicer changes later, the calculation table will not change.

You can try to use the measure.

DynamicTable =
var MetricSelected = SELECTEDVALUE( ParMETRICS[METRICS Order],4 ) 
var VirtualTable1 =
                CALCULATETABLE(
                                SELECTCOLUMNS(table1,field1,field2),
                                filter1)
var VirtualTable2 =
                CALCULATETABLE(

                                SELECTCOLUMNS(table1,field1,field2),
                                Filter2)   

Var CombinedTable =
                UNION(
                                SELECTCOLUMNS(VirtualTable1, field1,field2,
                                “Condition”,0     

                     ),
                                SELECTCOLUMNS(VirtualTable2 ,field1,field2,
                                “Condition”,1         
                              )
                    )
RETURN
Countrows(FILTER(CombinedTable,[Condition] = MetricSelected))

You can refer to the following similar thread.

Solved: Dynamic calculatable table - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.