Select a single cell in table

[Anonymous]

Helo everyone,

I have created few DAX measures which shows counts of various categories. I want user to click on certain cell so that they can see more details of the count on another table below. But the thing is whenever I click the whole row is getting selected and in another table I am getting all the values. 

I searched previous solutions many said it's not possible, is there any other alternative way to achieve?

Thanks,

Karthik

[Anonymous]

@MFelix  Your thoughts on this?

The metrics that you can see in table-1 are calculated from DAX and these are shown based on milestones. The table-2 shows a detailed information without any sort of aggregation (source for table-1). 

My requirement is if someone clicks on any number in table-1, automatically those records must be shown in table-1. As of now filter is happening based on rows (milestone) but I need the filter to work on the basis of counts (cell).

Is this possible to achieve?

Thanks,

Karthik

[MFelix]

Hi @Anonymous ,

This is related with the configuration of your matrix. When you add measure to a matrix you get a column visualization however there is no "column context" (probably not the best word) and when you select the values you are only getting the values where there is "context" in this case the row see image below:

Has you can see since I don't have column I can only select the entire row.

If you add a disconnected table with the measures names and a switch measure you can select individual cells since the "column context" is added to the matrix.

What I did in my case was to create the following table and measure:

SalesMEasuresValues = SWITCH(
                    SELECTEDVALUE(SalesMeasures[ID]),
                    1, [sales previous year],
                    2, Sales[sales previous year])

Now you need to use the measure has values on the matrix and the column no the table on the columns, also you need to add the measure to filter out the table visualization showing all the values that are not blank:

Has you can see now you can select individual values. In my case the calculations are very simple but in other models I was abble to filter out a list of customer based on the values of sales between certains min and max.

Check PBIX file attach.

[Anonymous]

Thank you @MFelix  but will the solution work if we have multiple DAX measures?

[MFelix]

Don't forget to mark the correct answer if that is the case, if you have any other questions please tell me.

[Anonymous]

Hello @MFelix ,

Apologies for getting back to you delayed, I was occupied.

I tried your solution and I was able to select individual cell rather than a whole row but still the problem did not solved.

From the above figure you can see matrix visual on the top and a normal table below. The idea here is if I click on any particular cell on the first table it must filter in second table. For example if I click "3" in the 9th ID of Last week - Forecast only 3 records must be shown in the bottom table.

From the second image now you can notice that I have selected value "3" in Last week - Forecast of ID 9. But lot of records are selected (ID 9 = Award), its still filtering based on the ID rather than showing individual results.

The measures in the first table "Last week - Forecast" and "Last Week - Plan" are indiviual DAX. It is just filtering data based on dates so I am pretty much not sure how to achieve this. Do I need to create a relationship table, please advise.

You have helped a lot but need this query to be resolved.

Thanks,

Karthik

[MFelix]

Hi @Anonymous ,

Have you used the measure you created for the matrix has a filter on the other visualization?

You need to place the measure has a filter of the second visualization and select all non blank values.

[MFelix]

Hi @Anonymous,

Yes I'm using only 2 measures but you can use has many has you want. 

[amitchandak]

@Anonymous , based on what I got. All the ungrouped/Unsummarized columns will get passed filter in case of drill down. You can control this, in case of drill through (You can switch off-  "keep all filter "and choose what you want to pass below it

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough