Forum Discussion
Control Field Parameters with Related Table?
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?
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.
1 Reply
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.