Forum Discussion

jmsny's avatar
jmsny
Frequent Visitor
5 months ago
Solved

Filter on Visual Row Context

Hi,

I'm working on a table that needs to show every name on a list and corresponding data, even when being filtered.  For example:

 

NameData
Name 11
Name 22
Name 33

 

That is how it shows when all names are selected, and is supposed to show always.  However, what is currently happening when a name is selected is this:

NameData
Name 1 
Name 22
Name 3 

 

Simplifying the parameter as a count, this is basically what I've done for the data column:

 

CALCULATE(COUNT('Table'[Data]),
FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])))

 

Which doesn't work.
If I filter on a different variable instead of SELECTEDVALUE for Name, it populates the entire table, but without row-specific values:
NameData
Name 16
Name 26
Name 36
 
How can I have it show all data and filter based on the row in the visual?

 

Need Help
  • jmsny's avatar
    jmsny
    5 months ago
    Ashish_Mathur wrote:

    Hi,

    Simplify your measure to

    =COUNT('Table'[Data])
    SamInogic wrote:

    Hi,

     

    As per understanding to are attempted to achieve is to always display all names in the visual, while the Data column still shows the correct value for each row, even when a slicer filters one name.

    The issue occurs because SELECTEDVALUE('Table'[Name]) is affected by the slicer context, which removes other names from the filter context. As a result, only the selected name returns data while the others appear blank.

    Recommended Approach

    Instead of using SELECTEDVALUE, remove the slicer filter on Name while keeping the row context from the visual.

    You can modify the measure like this:

    Data Measure =
    CALCULATE(
    COUNT('Table'[Data]),
    REMOVEFILTERS('Table'[Name])
    )

     

    This Works Because

    REMOVEFILTERS('Table'[Name]) removes the slicer filter on the Name column.
    • The row context from the table visual still applies, so each row calculates its own value.
    • This allows the visual to display all names with their corresponding data, even when a slicer selection exists.

     

    Hope this helps.

     

    Thanks!

     

    No, these are the opposite of what I wanted to do, and the second one smells like AI.

     

     

    I already mentioned what ended up working a few comments ago, but this is the DAX:

     

    CALCULATE(COUNT('Table'[Data]),
FILTER(ALL('Helper Table'),'Helper Table'[Name]=SELECTEDVALUE('Table'[Name])))

     

    The Helper Table is connected to the original table by Name and Helper Table[Name] was in the slicer.

     

9 Replies

  • danextian's avatar
    danextian
    Super User
    5 months ago

    Hi jmsny 

     

    Your measure doesn't work because it applies the same value for the whole of the table of the selected name. You can either remove the interaction or use a disconnected table.

    Please see the attached pbix.

    If this still doesn't work, please further elabarote your use case.

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    1) Create a disconnected Name table

    Name Slicer =
DISTINCT ( DimName[Name] )

    Do not create a relationship from Name Slicer to your model.

     

    2) Use fields like this

    Table visual rows: DimName[Name] (the “real” name dimension that drives the rows)

    Slicer: Name Slicer[Name]

     

    3) Measure (filters data using the slicer, but keeps all rows)

    Data Count :=
VAR SelNames = VALUES ( 'Name Slicer'[Name] )
RETURN
IF (
    ISFILTERED ( 'Name Slicer'[Name] ),
    CALCULATE (
        COUNT ( Fact[Data] ),               
        TREATAS ( SelNames, DimName[Name] ) 
    ),
    COUNT ( Fact[Data] )
)

     

    • jmsny's avatar
      jmsny
      Frequent Visitor
      5 months ago
      Ashish_Mathur wrote:

      Hi,

      Simplify your measure to

      =COUNT('Table'[Data])
      SamInogic wrote:

      Hi,

       

      As per understanding to are attempted to achieve is to always display all names in the visual, while the Data column still shows the correct value for each row, even when a slicer filters one name.

      The issue occurs because SELECTEDVALUE('Table'[Name]) is affected by the slicer context, which removes other names from the filter context. As a result, only the selected name returns data while the others appear blank.

      Recommended Approach

      Instead of using SELECTEDVALUE, remove the slicer filter on Name while keeping the row context from the visual.

      You can modify the measure like this:

      Data Measure =
      CALCULATE(
      COUNT('Table'[Data]),
      REMOVEFILTERS('Table'[Name])
      )

       

      This Works Because

      REMOVEFILTERS('Table'[Name]) removes the slicer filter on the Name column.
      • The row context from the table visual still applies, so each row calculates its own value.
      • This allows the visual to display all names with their corresponding data, even when a slicer selection exists.

       

      Hope this helps.

       

      Thanks!

       

      No, these are the opposite of what I wanted to do, and the second one smells like AI.

       

       

      I already mentioned what ended up working a few comments ago, but this is the DAX:

       

      CALCULATE(COUNT('Table'[Data]),
FILTER(ALL('Helper Table'),'Helper Table'[Name]=SELECTEDVALUE('Table'[Name])))

       

      The Helper Table is connected to the original table by Name and Helper Table[Name] was in the slicer.

       

  • zenisekd's avatar
    zenisekd
    Super User
    5 months ago

    Please try out this:

     
    Data Measure =
    CALCULATE(
    COUNT('Table'[Data]),
    ALL('Table'[Name])
    )
     
    or
     
    Data Measure =
    CALCULATE(
    COUNT('Table'[Data]),
    REMOVEFILTERS('Table'[Name])
    )
    • jmsny's avatar
      jmsny
      Frequent Visitor
      5 months ago

      That does not work.  It's similar to what I mentioned at the end:

       

      If I filter on a different variable instead of SELECTEDVALUE for Name, it populates the entire table, but without row-specific values

      I don't want it to show the same value for every name, I want it to show individual values.

  • jmsny's avatar
    jmsny
    Frequent Visitor
    5 months ago

    Update: the helper table I was using was the cause and solution of the problem.  I don't fully understand it since I tried both the original and helper tables as slicers, but the filter needs to be on the helper table and the selected value needs to be the original table.  It doesn't work the other way around even if the slicer is from the original table.

  • SamInogic's avatar
    SamInogic
    Super User
    5 months ago

    Hi,

     

    As per understanding to are attempted to achieve is to always display all names in the visual, while the Data column still shows the correct value for each row, even when a slicer filters one name.

    The issue occurs because SELECTEDVALUE('Table'[Name]) is affected by the slicer context, which removes other names from the filter context. As a result, only the selected name returns data while the others appear blank.

    Recommended Approach

    Instead of using SELECTEDVALUE, remove the slicer filter on Name while keeping the row context from the visual.

    You can modify the measure like this:

    Data Measure =
    CALCULATE(
    COUNT('Table'[Data]),
    REMOVEFILTERS('Table'[Name])
    )

     

    This Works Because

    REMOVEFILTERS('Table'[Name]) removes the slicer filter on the Name column.
    • The row context from the table visual still applies, so each row calculates its own value.
    • This allows the visual to display all names with their corresponding data, even when a slicer selection exists.

     

    Hope this helps.

     

    Thanks!