Forum Discussion
Helper IIIHave a table Appear only few values when a filter is selected
Hello Team,
I have a question about filtering the data. I have two columns 1. Name, 2. Status. Initially I would like to show the status of all the names (Approved, In process and Completed) data in a stacked column chart and a table.
Now I need to show only “Approved” and “In process” data of a particular person. So, I used a Selection slicer by walnut innovation here to show the names' column. Now, when I select a particular name, I should be able to show only “approved” and “In process” status in both table and column chart and not the “completed” status for that name, and when I clear the selection it should get back to normal. Is there any calculated column or measure I can add to show the data without breaking the file? I hope I was able to explain my needs.
Thanks in advance.
You're welcome! If this is helpful, please mark it as a solution 🤠
In order to get what you need, you will need to do the following:- In modeling, create a new table with the following DAX:
VALUES(Table[Name])And add this new column to a slicer on the page
- Create a measure with the following:
Measure = VAR _SelectedValue = SELECTEDVALUE('Table 2'[Name]) VAR _Filtered = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[Status] IN {"Approved", "In Progress"} && 'Table'[Name]=_SelectedValue))) VAR _All = CALCULATE(DISTINCTCOUNT('Table'[Name])) RETURN IF(ISBLANK(_SelectedValue),_All,_Filtered)- Lastly, on your table, add the measure filter to the visual filter pane and mark the filter as "is not blank"
You should get this as result:
Filtered:
15 Replies
h11parry2k Thank you for the response. But that is not my question. If I use a page - level filter, I should manually select the status whenever I want to see a set of statuses. I can still do that on my desktop for myself. But if I publish the file and people go through that file in a workspace. I want them to see the initial data as shown in screenshot 1 and then, by selecting their name, the approved and in process status data should automatically be shown instead of manually clicking on the filters to get the required data.
ExcelMonkeImpactful Individual
It looks like there is data though for the "completed" category? I.e. username "B" has data in the "completed section". Alternatively, you could create something along the lines of:
Measure = VAR _ChartValue = CALCULATE(COUNT(FactTable[Status]),FactTable[Status]="Approved" || FactTable[Status]="In Progress") RETURN IF( HASONEVALUE( SELECTEDVALUE(NameSlicer)), _ChartValue)- h11
ExcelMonke Thank you for the response. This is just a sample data, there are like 80+ names with multiple "approved" , "in progress" and "completed" projects for them. The formula should be able to help them see only approved and in progress projects when they select their name in the slicer.
I did try the above formula but fail to understand this section ( SONEVALUE(
SELECTEDVALUE(NameSlicer)),
_ChartValue). Can you please explain this section if the above formula is correct for my case or can you please share what exactly needs to be done in my case.Thank you.
- ExcelMonke
Apologies,
That DAX was incorrect. The following may give you your intended result:Measure = VAR _ChartValue = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[Status] IN {"Approved", "In Progress"})) RETURN IF(ISFILTERED('Table'[Name]),_ChartValue,COUNT('Table'[Name]))This is what happens when Name "A" is selected:
And here it is unselected:
- ExcelMonke
You're welcome! If this is helpful, please mark it as a solution 🤠
In order to get what you need, you will need to do the following:- In modeling, create a new table with the following DAX:
VALUES(Table[Name])And add this new column to a slicer on the page
- Create a measure with the following:
Measure = VAR _SelectedValue = SELECTEDVALUE('Table 2'[Name]) VAR _Filtered = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[Status] IN {"Approved", "In Progress"} && 'Table'[Name]=_SelectedValue))) VAR _All = CALCULATE(DISTINCTCOUNT('Table'[Name])) RETURN IF(ISBLANK(_SelectedValue),_All,_Filtered)- Lastly, on your table, add the measure filter to the visual filter pane and mark the filter as "is not blank"
You should get this as result:
Filtered:
- h11
ExcelMonke Hi
I followed the same steps as you mentioned above. While typing the measure I got this error. Please let me know what to do now?
Thank you!
- h11
ExcelMonke It worked!!! Thanks a ton!!
- ExcelMonke
Are the names of all your tables and columns correct? I would check those and make sure you double check the name of your Fact Table and columns
- h11
ExcelMonke Yes Sir. I double checked everything and used exact same as you used including table and column names. Still facing the same syntax error.
- h11
ExcelMonke Hi,
I double checked the data and also tried to create this visual in a new file by using the data you used in the screenshot. But no luck. I'm 100% sure that I can get the results by following your approach. Could you please help me resolve this issue or try an alternate to get the desired results. Appreciate your help!
Thanks
- ExcelMonke
Try the following:
Measure = VAR _SelectedValue = SELECTEDVALUE('Table 2'[Name]) VAR _Filtered = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[Status] IN {"Approved", "In Progress"}), FILTER('Table','Table'[Name]=_SelectedValue)) VAR _All = CALCULATE(DISTINCTCOUNT('Table'[Name])) RETURN IF(ISBLANK(_SelectedValue),_All,_Filtered)I am looking at my PBIX now and seeing it work without error
- h11
- ExcelMonke
Hmmm, the only other thing I can think of is, in the IF statement, replace the variable with 1 and 0 respectively. So:
IF(ISBLANK(_SelectedValue),1,0)And then replace it with the variables. This did work for me
