Forum Discussion

MJG2112's avatar
MJG2112
Advocate II
8 months ago
Solved

Using Field Parameters to Alter My Table Visual Content

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 ca...
  • KarinSzilagyi's avatar
    KarinSzilagyi
    8 months ago

    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's avatar
    KarinSzilagyi
    8 months ago

    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's avatar
    KarinSzilagyi
    7 months ago

    Hi MJG2112 sorry for the late reply. 
    As far as I understand the issue is due to the two separate table-filters. Try to combine both checks into a single Check-Measure instead that applies regardless of which columns are selected in your slicer, e.g.

    IE_RowFilter =
    VAR _mode = SELECTEDVALUE(Parameter_Incl_Excl[IE_Slicer])  -- => "Include"/"Exclude"
    VAR _x = [Measure_X]
    VAR _y = [Measure_Y]
    RETURN
    SWITCH (
        _mode,
        "Include", IF ( _x <> 0, 1, 0 ),
        "Exclude", IF ( _y <> 0, 1, 0 ),
        1
    )

    => remove the two filters for Measure_X and Measure_Y and add IE_Rowfilter = 1 instead.