How to Retrieve data dynamically based on user selection

[zhanghonwork]

Hi I have a request to build a report. There is a slicer that has two values in dropdown: include and exclude. When user choose the "include", the report will display all data. When user choose "exclude", the report will display data filtered out one department data. How do I achieve this in PowerBi? Thanks in advance. 

[v-priyankata]

Hi @zhanghonwork 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@Ashish_Mathur @audreygerred Thanks for the inputs.

I hope the information provided by users was helpful. please try those, If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @zhanghonwork 

I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @zhanghonwork 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[audreygerred]

Hello! Create a table with all departments listed. Put EXCLUDE in the column next to the name of the department you want to work as the exclude. You can put INCLUDE for the rest. Join this table to your fact table on department. In the report, if you use the include/exclude filter if a user selects nothing it will show them everything. If they select include it will show only the departments you have as include. If they select exclude it will only show those items marked as exclude.

[zhanghonwork]

Thanks audreygerred! The "include" will include all departments. See we have department 1,2,3,4,5. Wirh include, it need to show all data from department 1,2,3,4,5. With exclude, it will show 1,2,4,5 ( to exclue department 3 only). So the solution you provided will not work. Thnks anyway. 

[Ashish_Mathur]

HI,

In the table which has departments, create another column which shows include/exclude in each row.  Create an include/exclude slice from this new column.

[audreygerred · Accepted solution]

In Power BI, not selecting anything from the filter is the equivalent of showing everything (i.e. you are not filtering). If you want it so that when a selection is made only 1, 2, 4, 5 show up - the user would select INCLUDE. If they select EXCLUDE it would show 3. No filter show 1, 2, 3, 4, 5. You can change the verbiage and have include be exclude and vise versa, but to show all items - you filter nothing.