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!

[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).

[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]))

}

[KarinSzilagyi]

Hi @MJG2112, since you want to change out a group of columns with a single field (A or B) you need to add an additional column to your field-parameter to group the columns you want to display. 

Example:

To add additional columns you just need to add a comma after the sort-order (0, 1, 2 etc) but before the closing round bracket of each field in your field parameter. It's important to consider that you need to use the same number of columns for each row though! You can rename the new Column via the Data pane:

The column containing "Another column" as a value was added as "Value5" in this example.

[MJG2112]

Hi @KarinSzilagyi I've not got this right as I didn't get the desired results.  This is the Parameter I created, which gave me a slicer with the Value A1 and Value A2 options, and I had to select both to make both columns appear in the visual.  I couldn’t make Value B1 and Value B2 appear.  What I want is the slicer to let me select either option A or option B.  If I select option A then Value A1 and Value A2 are displayed, and if I select option B then Value B1 and Value B2 are displayed.

[KarinSzilagyi]

Hi @MJG2112  Try this:

AorB = {

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

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

}