Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

How to filter a table visual when the user clicks on a matrix value that is a measure?

Hi, I have a data table that contains many fields. These fields are placed in a table visual. Upon this data table, I create a DAX measure (as below) and this measure, along with other measures, ar...
Need Help
  • HarishKM's avatar
    HarishKM
    1 year ago

    D_PBI Hey,
    I have tried to recreate similer measure at my end.
    I am using this 

    Number group by date level(Cumulative) =
    CALCULATE (
        [PatentCount],  
        FILTER (
            ALL(PatientTable),  -- Remove filters to consider all rows in the table
            NOT (
                ISBLANK(PatientTable[FY]) || PatientTable[FY] = ""  -- Skip blank or empty FY
            ) &&
            VALUE(LEFT(PatientTable[FY], 4)) <= VALUE(LEFT(MAX(PatientTable[FY]), 4))  -- Compare years up to current max year
        )
    )
    when I select any perticular FY then my data is filtering as expected

    Could you try above new measure cummulative sum?

     

    Thanks
    Harish M
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

     

     

HarishKM's avatar
HarishKM
Icon for Super User rankSuper User
1 year ago

D_PBI Hey,
you need to create a new measure

1st Measure
PatentCount = DISTINCTCOUNT(PatentTable[PatentID])

 

2nd measure 

FilterPatentContext =
VAR CurrentPatentIDs =
CALCULATETABLE(
VALUES(PatentTable[PatentID]),
REMOVEFILTERS(PatentTable[PatentID])
)
RETURN
IF (
COUNTROWS(
INTERSECT(
VALUES(PatentTable[PatentID]),
CurrentPatentIDs
)
) > 0,
TRUE,
FALSE
)


step to follow post both measure creation

Add a Table visual to your report with the detailed fields:
PatentTable[PatentID], Category, Country, etc.
Go to the Filters pane (on the right) for that table visual.
Drag FilterPatentContext into the Visual Level Filters section.
Set it to is TRUE.

 

above solution will give you expected result

 

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

  • D_PBI's avatar
    D_PBI
    Icon for Post Partisan rankPost Partisan
    1 year ago

    HarishKM - (this is an amended message to my initial response) Thank you for your reponse. I've created and configured what you advised, however, when dragging the FilterPatentContext measure into the Visual Filter section, there is no option to set it to True. Possibly due to the measure always returning True (which I don't think should be the case)??
    It's hard to explain without providing you with a .pbix file which I cannot do due to senstive data.
    Having read further, I don't think my intention is possible. Other PBI Communty threads are confirming such. To, hopefully, be a little clearer, I am wanting to create a measure, this measure will be using CALCULATE and distinct counting a column's values, and this measure will also be having some additional filtering within it. This measure, when placed in a matrix visual, using the date row evaluation, will return a number, say 10. My aim is that when the user clicks on 10, a table visual, which is also present on the page, has its rows reduced to show only the ten rows that comprise that figure 10.
    My understanding is creating a measure that uses the CALCULATE function, or goes beyond the simple use of COUNT/SUM,etc.. and has other filtering applied, then this other filtering cannot be applied to table visuals to reduce the shown rows down.

    • HarishKM's avatar
      HarishKM
      Icon for Super User rankSuper User
      1 year ago

      D_PBI Hey,
      I have tried to recreate similer measure at my end.
      I am using this 

      Number group by date level(Cumulative) =
      CALCULATE (
          [PatentCount],  
          FILTER (
              ALL(PatientTable),  -- Remove filters to consider all rows in the table
              NOT (
                  ISBLANK(PatientTable[FY]) || PatientTable[FY] = ""  -- Skip blank or empty FY
              ) &&
              VALUE(LEFT(PatientTable[FY], 4)) <= VALUE(LEFT(MAX(PatientTable[FY]), 4))  -- Compare years up to current max year
          )
      )
      when I select any perticular FY then my data is filtering as expected

      Could you try above new measure cummulative sum?

       

      Thanks
      Harish M
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query