Forum Discussion
Select a single cell in table
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
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.
Thank you MFelix but will the solution work if we have multiple DAX measures?
Hi Anonymous,
Yes I'm using only 2 measures but you can use has many has you want.
Don't forget to mark the correct answer if that is the case, if you have any other questions please tell me.
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