Forum Discussion

chyang0107's avatar
chyang0107
Icon for Helper I rankHelper I
11 months ago
Solved

How to hide the values in the Matrix based on the condition

Hello,

 

I have a question on how to hide/collapse the four names (listed in the image below)and corresponding values if the name "Distinct Subaward Count" has the value equal number 0 under the visualization "Matrix". The backgorund information of this settring, these four names were added to "Values" under the "Matrix" under the "Visualizations". In addition, this matrix is also afftected to another slicer. 

 

Under the 'Filter', The option 'Show items then this item' is not selectable ('is less than') and the field below is grey out. The filter is not working on my end.

 

Your insights are appreciated.

Thanks,

Hank

 

  

 

 

 

 

Need Help
Show and Tell
  • Ritaf1983's avatar
    Ritaf1983
    11 months ago

    Hi chyang0107 

    It’s not possible to filter measures directly based on a condition. Filters work at the level of a single column in a table or a single measure output, but you can’t filter out other measures placed in Values.

    What you can do is create an “imitation of a measure table” and use a dynamic measure.

    For example, if you have measures:
    a, b, c, d

    You can build a table like in the picture and then a dynamic measure:

    dynamicMeasure = 
    var selected_ =SELECTEDVALUE('Table'[column])
    RETURN
    SWITCH(selected_,
    "a",[a],
    "b",[b],
    "c",[c],
    "d",[d],
    BLANK())

    Then it can be filtered…

    If the filtering condition is fixed (and not something the user is expected to control), you can also apply the logic directly inside the formula:

    dynamicMeasure2 =

    var selected_ =SELECTEDVALUE('Table'[column])

    RETURN

    SWITCH(TRUE(),

    selected_="a"&& [a]<12,[a],

    selected_="b"&& [b]<12,[b],

    selected_="c"&& [c]<12,[c],

    selected_="d"&& [d]<12,[d],

    BLANK())

     

    In addition, you could consider Field Parameters, which work under a similar logic but are easier to configure in the UI, or Calculation Groups in Tabular Editor, which are more powerful but require extra setup.

    The pbix with the example is attached

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

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    11 months ago

    Consider using a composite measure, or using Field Parameters.  Make sure your business users have asked for that feature.

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    11 months ago

    Hi chyang0107 

    It’s not possible to filter measures directly based on a condition. Filters work at the level of a single column in a table or a single measure output, but you can’t filter out other measures placed in Values.

    What you can do is create an “imitation of a measure table” and use a dynamic measure.

    For example, if you have measures:
    a, b, c, d

    You can build a table like in the picture and then a dynamic measure:

    dynamicMeasure = 
    var selected_ =SELECTEDVALUE('Table'[column])
    RETURN
    SWITCH(selected_,
    "a",[a],
    "b",[b],
    "c",[c],
    "d",[d],
    BLANK())

    Then it can be filtered…

    If the filtering condition is fixed (and not something the user is expected to control), you can also apply the logic directly inside the formula:

    dynamicMeasure2 =

    var selected_ =SELECTEDVALUE('Table'[column])

    RETURN

    SWITCH(TRUE(),

    selected_="a"&& [a]<12,[a],

    selected_="b"&& [b]<12,[b],

    selected_="c"&& [c]<12,[c],

    selected_="d"&& [d]<12,[d],

    BLANK())

     

    In addition, you could consider Field Parameters, which work under a similar logic but are easier to configure in the UI, or Calculation Groups in Tabular Editor, which are more powerful but require extra setup.

    The pbix with the example is attached

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

    • chyang0107's avatar
      chyang0107
      Icon for Helper I rankHelper I
      11 months ago

      Hello Rita,

      I appreicate your detailed information and insights provided. Currently, I am using the Matrix (Not Table) to contain the information of interest (the information are placed under 'Value') and hopefully to hide these values based on the condition. Could I know if your suggestions measure or Field Parameters still work for my visualization?

       

      Thanks,

      Hank

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        11 months ago

        Hi chyang0107 
        My solution will work with all kind of visuals.
        "Column " should go to columns
        Dynamic measures to "values"

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

  • v-lgarikapat's avatar
    v-lgarikapat
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi chyang0107 ,

    Thanks for reaching out to the Microsoft fabric community forum

    Ritaf1983 , lbendlin 

    Thanks for your prompt response

    chyang0107 

    I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by  Ritaf1983 , lbendlin . If you have any questions or need further clarification, please don’t hesitate to reach out.

    We appreciate your collaboration and support!

    Best regards,
    Lakshmi.