Control Field Parameters with Related Table?

[DavidKuhry]

Simple Version:

Essentially, I want to use a related custom table, to filter the field parameter tables in order to build a chart with a custom Y and X axis using predefined styles. 

Have one Field Parameter containing measures that would be put in the Y axis, and the other containing DIMs in the X. When I select a value from the custom table, say "Sales by Color" it would filter the 2 param tables and create a chart of Sales by Color. 

My actual use case: 

So I have a set of Field Parameters that I use to build the X and Y axis of a chart. If I do this in the normal way of having a slicer for each of them, then it works.

But I want to use a custom table in the middle with a relationship to each field param table so that based on the selection from the custom table, it'll filter the 2 parameters to values that match and use those in the table.

Param 1

Value	Expression	Order
Value 1.a	Exp1	0
Value 1.b	Exp2	1
Value 1.c	Exp3	2

Param 2

Value	Expression	Order
Value 2.a	Exp1	0
Value 2.b	Exp2	1
Value 2.c	Exp3	2

Custom Table

Value	Param 1 Key	Param 2 key
Custom 1	0	0
Custom 2	0	1
Custom 3	1	2

Relationships are from the Custom Table Key to the applicable Param table Order columns.

So if I put the Value column from the Custom Table into a slicer:

• Custom 1 would filter to 1.a and 2.a

• Custom 2 would filter to 1.a and 2.b

• Custom 3 would filter to 1.b and 2.c

Is there a way to get this to control the field parameter values in my chart? Currently it does not even though they are the only values. Is there a way to "select" them without directly selecting them in a slicer?

[Anonymous · Accepted solution]

Hi @DavidKuhry ,

You can try to use measure to achieve the effect.
1. Create a calculation sheet.

Table = GENERATE(SELECTCOLUMNS('Table1',"Value1",'Table1'[Value],"Order1",'Table1'[Order]),SELECTCOLUMNS('Table2',"Value2",'Table2'[Value], "Order2",'Table2'[Order]))

2. Create measure.

Measure =
VAR _value1 = SELECTEDVALUE('Table'[Value1])
VAR _value2 = SELECTEDVALUE('Table'[Value2])
RETURN
SWITCH(
TRUE(),
_value1="Value 1.a"&&_value2="Value 2.a","Custom1",
_value1="Value 1.a"&&_value2="Value 2.b","Custom2",
_value1="Value 1.b"&&_value2="Value 2.c","Custom3"
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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