Have a table Appear only few values when a filter is selected

[h11]

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.

[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

[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 Still the same issue. 

[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 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

[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.

[ExcelMonke · Accepted solution]

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:

1. 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 It worked!!! Thanks a ton!!

[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!

[ExcelMonke]

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:

[h11]

@ExcelMonke Thank you. I guess i'm almost reaching there. However, I still have 2 questions.

1. I'm still able to see completed status in the table even after you applied that DAX as shown in the picture you posted. I don't want to see the completed status neither in column chart nor in the table once I select a name.

2. After the measure is added, to which visual do I add this measure to get the desired output is it the Name slicer or the table or the column chart?

Thanks again for putting your efforts to help me get my issue resolved.

[h11]

@parry2k 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. 

[parry2k]

@h11 you can add a page-level filter and select statuses that you need, and all the visual on the page will show only those statuses.