Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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 

 

DateProduct DeliveredProduct ProcessedCompany_Supplier_Name
01/01/2020127039176102Company A
08/01/2020308639358319Company B
15/01/20204000643606Company C
22/01/20206011160966Company D
29/01/20206331963178Company E
05/02/202028763050554Company F
12/02/2020153938153824Company G
19/02/20202140021400Company H
26/02/202018927914888Company I
04/03/20203087891408Company J
11/03/20208902789008Company K

 

If i click on the slicer at the top of the page e.g. "Company K" is there a way to have the total list of suppliers remain in the slicer (A:K) While having the table dynamically cahnge to just show "Company K" and "All others"

Dynamic Table Change.GIF

I have: 

Company Supplier Sort = IF([Company_Supplier_Name] = "Company K" || [Company_Supplier_Name] = "Company K",[Company_Supplier_Name], "All Other")
 
But is says "The value for 'Company_Supplier_Name' cannot be determined. Either the column doesn't exist or there is no current row for this column.
 
 

 

 

I'm currently using data via Direct Query so I would need a DAX formula as the addition of calculated column might not work.https://community.powerbi.com/t5/Desktop/Dynamically-changing-column-attributes-in-the-table/m-p/938...

 

 

Desired Outcome:Dynamic Table Change Desired Outcome.GIF

 

https://app.powerbi.com/groups/me/reports/c12a6ebd-2016-4fe2-827f-58b6759f3bcd?ctid=0727f0b0-9d54-42...

2 REPLIES 2
123abc
Community Champion
Community Champion

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.

 
technolog
Super User
Super User

The error you're seeing is because you're trying to reference a column directly in a measure without an aggregation function. In DAX, you can't directly reference a column's value without some context, like SUM, MAX, MIN, etc.

To achieve your desired outcome, you can use a combination of measures and calculated columns. But since you're on Direct Query mode, adding a calculated column might not be the best option. So, we'll focus on creating a measure.

First, let's create a measure to capture the selected value from the slicer:

SelectedCompany =
SELECTEDVALUE('YourTableName'[Company_Supplier_Name], "All")
This measure will return the selected company name from the slicer or "All" if nothing is selected.

Now, let's create another measure to determine if the current row should be displayed:

DisplayCompany =
IF(
[SelectedCompany] = "All" || 'YourTableName'[Company_Supplier_Name] = [SelectedCompany],
'YourTableName'[Company_Supplier_Name],
"All Others"
)
Now, you can use this DisplayCompany measure in your table visualization. When you select a company from the slicer, the table will show rows for that company and "All Others". When no company is selected, it will show all companies.

Lastly, to ensure that your table only displays the selected company and "All Others", you might need to adjust the table's filter or visual-level filter to only include rows where the DisplayCompany measure is not blank.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.