Using Field Parameters to Alter My Table Visual Content

[MJG2112]

I have a need to display one of two sets of columns in the same table visual depending on user choice.  I discovered I can (probably) do this using Field Parameters, but I'm not sure exactly how I can acheive it.  I had a play and managed to get something to partly work, but it was only showing of the two columns and not the totals.  I want a slicer to let the user select values A or B (from Table 1) and the table visual show only the selected columns.  Is this achievable?   My mock up data will help explain better.

[KarinSzilagyi · Accepted solution]

Hi  @MJG2112, is "GL Local Cost (Incl FF)"  a column or a measure? If it's a measure the total should work, if it's a column it probably doesn't work because Power BI doesn't know that it needs to apply an aggregation on it. I just tested it locally:

• "Sum of ValueStatic" (blue) uses the column directly the aggregation applied
• "Value A1" - B2 are Measures added to the Field-Parameter:

Value A1 = sum(ParameterTest[ValueA1])​

AorB = {
    ("Value A1", NAMEOF('ParameterTest'[Value A1]), 0, "A"), -- Measure "Value A1"
    ("Value A2", NAMEOF('ParameterTest'[Value A2]), 1, "A"), -- Measure "Value A2"
    ("Value B1", NAMEOF('ParameterTest'[Value B1]), 2, "B"), -- Measure "Value B1"
    ("Value B2", NAMEOF('ParameterTest'[Value B2]), 3, "B"), -- Measure "Value B2"
    ("Value Static as a column", NAMEOF('ParameterTest'[ValueStatic]), 3, "B") -- Column "ValueStatic"
}

• "Value Static as a column" is the Column "ValueStatic" added directly to the field parameter.

Conclusion: If you add a Measure for each Column you want to use via the Field-Parameter and use the Measures instead of the Column directly in the Field-Parameter, the Row Total will work again!

[MJG2112]

@KarinSzilagyi Sorry, I mean row totals. If you look at this view, the column on the left is referenced directly from the table.  The one on the right is the same column referenced via the parameter.  It has no totals.

[KarinSzilagyi · Accepted solution]

Hi  @MJG2112, is "GL Local Cost (Incl FF)"  a column or a measure? If it's a measure the total should work, if it's a column it probably doesn't work because Power BI doesn't know that it needs to apply an aggregation on it. I just tested it locally:

• "Sum of ValueStatic" (blue) uses the column directly the aggregation applied
• "Value A1" - B2 are Measures added to the Field-Parameter:

Value A1 = sum(ParameterTest[ValueA1])​

AorB = {
    ("Value A1", NAMEOF('ParameterTest'[Value A1]), 0, "A"), -- Measure "Value A1"
    ("Value A2", NAMEOF('ParameterTest'[Value A2]), 1, "A"), -- Measure "Value A2"
    ("Value B1", NAMEOF('ParameterTest'[Value B1]), 2, "B"), -- Measure "Value B1"
    ("Value B2", NAMEOF('ParameterTest'[Value B2]), 3, "B"), -- Measure "Value B2"
    ("Value Static as a column", NAMEOF('ParameterTest'[ValueStatic]), 3, "B") -- Column "ValueStatic"
}

• "Value Static as a column" is the Column "ValueStatic" added directly to the field parameter.

Conclusion: If you add a Measure for each Column you want to use via the Field-Parameter and use the Measures instead of the Column directly in the Field-Parameter, the Row Total will work again!

[MJG2112]

@KarinSzilagyi Thank you so much Karin.  You a genius and a star.  That's worked perfectly and I've learned so much.

[KarinSzilagyi]

I'm glad I could help! 💚

[MJG2112]

@KarinSzilagyi Hi Karin. I think I know the answer will be 'NO', but is it possible to use the same Parameter construct to influence a different visual on a different page?  What I'd like to do is sync the slicer element to another page but have it toggle different table columns.

Thanks, Mark.

[KarinSzilagyi]

Hi @MJG2112, if you use the same fields on the other page and apply "synch slicers", this should actually work! 

[MJG2112]

@KarinSzilagyi It will be different fields, so I guess it won't work.  I wanted to avoid having a second slicer that looks exactly like the first one but isn't becuase it's not synced.

[KarinSzilagyi · Accepted solution]

You could make it work by giving those other fields thr same categories as on your first page + adding another column in your field parameter to add on page or visual level to filter out which of that subset of columns/measures should be displayed on each page. E.g.:

AorB = {

("Value A1", NAMEOF('Table 1'[Value A1]), 0, "A", "Page 1"),

("Value A2", NAMEOF('Table 1'[Value A2]), 1, "A", "Page 1"),

("Value B1", NAMEOF('Table 1'[Value B1]), 2, "B", "Page 1"),

("Value B2", NAMEOF('Table 1'[Value B2]), 3, "B", "Page 1"),

("Value 1 for Page 2", NAMEOF('Table 1'[Value A1]), 4, "A", "Page 2"),

("Value 2 for Page 2", NAMEOF('Table 1'[Value A2]), 5, "A", "Page 2"),

("Value 3 for Page 2", NAMEOF('Table 1'[Value B1]), 6 "B", "Page 2"),

("Value 4 for Page 2", NAMEOF('Table 1'[Value B2]), 7, "B", "Page 2")

}

=> you'd keep your slicer on "Value4" (if you didn't rename the column) and synch it accross both pages and would add an additional Filter via the Filter Pane for "Value5" to filter for "Page 1" or "Page 2" to only show the exact columns/Measures meant for each page (you can change it to whatever text you want of course).

(Sorry for the terrible formatting - I'm on mobile atm).

[MJG2112]

@KarinSzilagyi This works but there's a small problem I'm struggling to fix.  The 'value 5' filter works alongside the 'value 4' slicer as you described.  The problem I have now is with the two columns I'm swapping in and out on 'page 2'.  They both contain a lot of zero values, which I'd prefer to filter out, but whatever combination of "is not 0" filters I use I always end up filtering out too many as they seem to crossover.  Any thoughts on how to overcome this?

[KarinSzilagyi]

Did you try to filter them via hard-coded filters in the filter-pane on specific columns, or also via a filter measure, where you'd set up the condition as an if-statement (or switch) to return 1 or 0 for the rows that you want to filter, then applying that filter on the measure instead of a column?

[MJG2112]

@KarinSzilagyi It was in the filter pane on the measures.  

[KarinSzilagyi]

@MJG2112 Could you share what measure you used to filter them? Did you also try to display the measure in the table itself, to confirm whether the filter-measure is working correctly on all rows?

[MJG2112]

Parameter_Incl_Excl = {

("A", NAMEOF(‘Table 1’[Measure_A]), 0, "Include", "‘View 1’"),

("B", NAMEOF(‘Table 1’[Measure_B]), 1, "Include", "‘View 1’"),

("C", NAMEOF(‘Table 1’[Measure_C]), 2, "Include", "‘View 1’"),

("D", NAMEOF(‘Table 1’[Measure_D]), 3, "Exclude", "‘View 1’"),

("B", NAMEOF(‘Table 1’[Measure_C]), 4, "Exclude", "‘View 1’"),

("E", NAMEOF(‘Table 1’[Measure_E]), 5, "Exclude", "‘View 1’"),

("Z", NAMEOF(‘Table 2’[Measure_X]), 6, "Include", "‘View 2’"),

("Z", NAMEOF(‘Table 2’[Measure_Y]), 7, "Exclude", "‘View 2’")

}

Parameter_Incl_Excl	Parameter_Incl_Excl Fields	Parameter_Incl_Excl Order	IE_Slicer	IE_Page_Filter
A	Table 1’[Measure_A]	0	Include	View 1
B	Table 1’[Measure_B]	1	Include	View 1
C	Table 1’[Measure_C]	2	Include	View 1
D	Table 1’[Measure_D]	3	Exclude	View 1
C	Table 1’[Measure_C]	4	Exclude	View 1
E	Table 1’[Measure_E]	5	Exclude	View 1
Z	Table 2’[Measure_X]	6	Include	View 2
Z	Table 2’[Measure_Y]	7	Exclude	View 2

Measure_X = SUM(‘Table 2’[X]

Measure_Y = SUM(‘Table 2’[Y]

I have a Pager Filter IE_Page_Filter is View 2

I have two Visual Filters Table 2’[Measure_X] is not 0 and Table 2’[Measure_Y] is not 0

I have IE_Slicer synced on the page.

Here’s some sample data:

Team	X	Y
RED1	7	7
RED2	7	4
RED3	9	4
RED4	3	0
BLUE1	1	9
BLUE2	9	2
BLUE3	2	0
BLUE4	6	3
BLUE5	3	1
GREEN1	3	0
GREEN2	2	5
GREEN3	5	1
GREEN4	3	1
GREEN5	2	0
GREEN6	10	3
YELLOW1	0	5
YELLOW2	2	6
YELLOW3	8	0
YELLOW4	3	2
YELLOW5	9	8
YELLOW6	10	10
YELLOW7	2	5
YELLOW8	4	8

When IE_Slicer is Exclude then my visual is fine. The zero rows RED4, BLUE 3, GREEN1, GREEN5 and YELLOW3 are not displayed.

Team	Z
RED1	7
RED2	4
RED3	4
BLUE1	9
BLUE2	2
BLUE4	3
BLUE5	1
GREEN2	5
GREEN3	1
GREEN4	1
GREEN6	3
YELLOW1	5
YELLOW2	6
YELLOW4	2
YELLOW5	8
YELLOW6	10
YELLOW7	5
YELLOW8	8
	84

When IE_Slicer is Include then my visual is not fine.  YELLOW1 is not displayed (as expected) but RED4, BLUE 3, GREEN1, GREEN5 and YELLOW3 are also not displayed (not as expected). I know this is because I have both measures as Visual Filters, but I don’t know how to get it right.

Team	Z
RED1	7
RED2	7
RED3	9
BLUE1	1
BLUE2	9
BLUE4	6
BLUE5	3
GREEN2	2
GREEN3	5
GREEN4	3
GREEN6	10
YELLOW2	2
YELLOW4	3
YELLOW5	9
YELLOW6	10
YELLOW7	2
YELLOW8	4
	92

[MJG2112]

Hi @KarinSzilagyi Thank you for being so helpful and patient.  I'm not getting this to work, so think it's best I share the full screen.  I've had to blank out most of it for confidentiality.  As you can see, I think I've got my Parameter set up in the way you have suggested.  My slicer is currently multi-select so I can select the two "A" columns or two "B" columns, because the single select only allows one of the four columns.  I want a single select A or B for both A and B columns.  I'm using the parameter in the visual columns, but there's no summarise option, so the column totals do not appear.  What am I getting wrong here in terms of the slicer and column totals?

[KarinSzilagyi]

@MJG2112 You're really close already!
You just have to use "FF_Incl_Excl" in your Table visual and the column "Value4" in your Slicer:

[MJG2112]

@KarinSzilagyi Thank you!  That worked and now I have the single select slicer on.  The only thing not working is the column totals.  Is that something that does not work when using parameters?

[KarinSzilagyi]

@MJG2112 Column or Row Totals? In your image you're currently using a table, not a matrix - only the Matrix supports Column Totals.

Here's an example using two tables - the left one uses the individual columns/measures directly, the right one two static ones + the Field Parameter:

Oddly enough only the static value on the right table isn't showing the total while it works on the left one.

[MJG2112]

@KarinSzilagyi I'm almost there. Using the code you shared gives me this Single Select slicer.  What I need is for it to just be 2 options (A and B) that display Value A1 and Value A2 (option A) or Value B1 and Value B2 (option B).

[KarinSzilagyi]

Hi @MJG2112, you're currently using the values from the column "AorB" (same name as your table unless you changed the name). You need to use the other column (probably called "Value4" if your Power BI is in english).
I made a quick example:

You can rename "Value4" via any of the rename options (right click + rename, column tools => rename, Model View etc.) if you want.

You can confirm the correct column name if you look at the FieldParameter in the Table View:

[MJG2112]

@KarinSzilagyi 

I have realised part of my error.  I've amended the Parameter to this and make the slicer Single Select.  This lets me choose A or B, but only displays Value A1 or Value B1.  Plus the columns do not show totals at the bottom.

AorB = {

    ("Value A1", NAMEOF('Table 1'[Value A1]), 0, "Value A2", NAMEOF('Table 1'[Value A2])),

    ("Value B1", NAMEOF('Table 1'[Value B1]), 1, "Value B2", NAMEOF('Table 1'[Value B2]))

}