Dynamic Filter to change in table DAX Direct Query

[Anonymous]

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/2020	308639	358319	Company B
15/01/2020	40006	43606	Company C
22/01/2020	60111	60966	Company D
29/01/2020	63319	63178	Company E
05/02/2020	287630	50554	Company F
12/02/2020	153938	153824	Company G
19/02/2020	21400	21400	Company H
26/02/2020	189279	14888	Company I
04/03/2020	30878	91408	Company J
11/03/2020	89027	89008	Company 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"

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:

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

[123abc]

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]

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.