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

[D_PBI]

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, are placed in a matrix visual (see further below).

When an end-user clicks on, for example, the figure/cell that is 33, I would like the table visual to be reduce down to show only those 33 records from the patent table. However, it doesn't do this.
It would seem as the DAX measure is distinct counting the patentid, it contains a number only and loses reference to the actual patentids. However, the list of patentids themselves are needed to be able to filter the table visual by patentid. Correct me if this isn't the reason why my itention isn't working.
Going on the basis it is, is it possible to create such a DAX measure, place it within a matrix, along with similar measures, and have a table visual filtered based on the measure/cell the end-user clicks on?

Hope this makes sense. Thanks.

[v-echaithra]

Hi @D_PBI ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @D_PBI ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @D_PBI ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithra E.

[HarishKM]

@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]

@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 · Accepted solution]

@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