Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic Filter to change in table DAX Direct Query

I have a slicer for supplier name on the top of a page and I have a table    Date Product Delivered Product Processed Company_Supplier_Name 01/01/2020 127039 176102 Company A 08/01/2...
Need Help
123abc's avatar
123abc
Community Champion
2 years ago

To achieve your desired outcome of dynamically changing the table in Power BI using a slicer while keeping all supplier names in the slicer, you can use a combination of DAX measures and the slicer.

Here are the steps to implement this:

  1. Create a Slicer: Create a slicer on your report page that allows users to select the desired supplier. This slicer will control what is displayed in the table.

  2. Create DAX Measures:

    • Create a measure to calculate the total for the selected supplier.
    • Create a measure to calculate the total for all other suppliers.

For example:

 

SelectedSupplierTotal = VAR SelectedSupplier = SELECTEDVALUE('SlicerTable'[Supplier]) RETURN CALCULATE(SUM('YourTable'[Product Delivered]), 'YourTable'[Company_Supplier_Name] = SelectedSupplier) AllOtherSuppliersTotal = VAR SelectedSupplier = SELECTEDVALUE('SlicerTable'[Supplier]) RETURN CALCULATE(SUM('YourTable'[Product Delivered]), 'YourTable'[Company_Supplier_Name] <> SelectedSupplier)

 

  1. Create a Table: Create a table visual on your report page that displays the data. In the Values section of the table, use the SelectedSupplierTotal and AllOtherSuppliersTotal measures that you created.

  2. Set Table Filters: In the table visual, apply the following filters:

    • For the SelectedSupplierTotal measure, set a filter condition to display when SelectedSupplierTotal is greater than 0.
    • For the AllOtherSuppliersTotal measure, set a filter condition to display when AllOtherSuppliersTotal is greater than 0.

Now, when a user selects a supplier from the slicer, the table will dynamically change to display the selected supplier's data and "All Others." When "All Suppliers" is selected in the slicer, the table will show all suppliers' data.

Ensure that you replace 'YourTable' with the actual name of your table, and 'SlicerTable' with the name of your slicer table.

This approach leverages DAX measures to calculate the totals dynamically based on the selected supplier and uses table filters to control the table's visibility.