Forum Discussion
Problem with filters
Hi
I have this column in a Power BI table with those possible values:
| conditions |
| Colab |
| ColabCustomers |
| Customers |
| CustomersGLUCUBERS |
| CustomersHyper |
| GLUCUBERS |
| GLUCUBERSTest |
| Hyper |
| PRUEBA |
| Test |
I want to create a filter with only the values: test, glucubers, customers, prueba, colab, and hyper. When I apply the filter, I want it to filter that table. Here's the thing: I want it so that if I filter for customers, only customers appears and not something like customersGlucubers. However, if I select two values in the filter, for example customers and glucubers, the table should be filtered by the values customers, glucubers, and their combination, in this case customersGlucubers.
Anyone could help me.
Thanks!
Hi Luisch - create a calculated column that identifies the rows based on your filtering logic
FilterFlag =
VAR SelectedValues = {"test", "glucubers", "customers", "prueba", "colab", "hyper"}
VAR ConditionLowerCase = LOWER('YourTable'[conditions])
RETURN
IF(
COUNTROWS(
FILTER(
SELECTCOLUMNS(
SelectedValues,
"SelectedValue",
[Value]
),
ConditionLowerCase = [SelectedValue] ||
LEFT(ConditionLowerCase, LEN([SelectedValue])) = [SelectedValue]
)
) > 0,
TRUE,
FALSE
)Use the above calculated column FilterFlag i, when you are in use in your visuals.
Apply the FilterFlag column to the visual-level or page-level filters and set it to filter for TRUE. This will ensure only rows that match your criteria are displayed.The slicer based on Filteroption allows users to select the criteriacreate a disconnected table:
Code:
FilterOptions =
DATATABLE(
"FilterValue", STRING,
{
{"test"},
{"glucubers"},
{"customers"},
{"prueba"},
{"colab"},
{"hyper"}
}
)filter the main table based on multiple selections from the slicer, you can create a measure that checks for the presence of each selected value and then filters the main table
FilteredValues =
VAR SelectedItems = VALUES(FilterOptions[FilterValue])
RETURN
IF(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[FilterFlag] = TRUE &&
(
ISBLANK(SELECTEDVALUE('YourTable'[conditions])) ||
CONCATENATEX(SelectedItems, [FilterValue], ",", 'YourTable'[conditions]) = [conditions]
)
)
) > 0,
1,
0
)You can then use this measure as a visual filter or conditional format in your visuals to highlight or display only the relevant rows
Hope this measure filtered values ensures the table filters correctly based
2 Replies
Hi Luisch - create a calculated column that identifies the rows based on your filtering logic
FilterFlag =
VAR SelectedValues = {"test", "glucubers", "customers", "prueba", "colab", "hyper"}
VAR ConditionLowerCase = LOWER('YourTable'[conditions])
RETURN
IF(
COUNTROWS(
FILTER(
SELECTCOLUMNS(
SelectedValues,
"SelectedValue",
[Value]
),
ConditionLowerCase = [SelectedValue] ||
LEFT(ConditionLowerCase, LEN([SelectedValue])) = [SelectedValue]
)
) > 0,
TRUE,
FALSE
)Use the above calculated column FilterFlag i, when you are in use in your visuals.
Apply the FilterFlag column to the visual-level or page-level filters and set it to filter for TRUE. This will ensure only rows that match your criteria are displayed.The slicer based on Filteroption allows users to select the criteriacreate a disconnected table:
Code:
FilterOptions =
DATATABLE(
"FilterValue", STRING,
{
{"test"},
{"glucubers"},
{"customers"},
{"prueba"},
{"colab"},
{"hyper"}
}
)filter the main table based on multiple selections from the slicer, you can create a measure that checks for the presence of each selected value and then filters the main table
FilteredValues =
VAR SelectedItems = VALUES(FilterOptions[FilterValue])
RETURN
IF(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[FilterFlag] = TRUE &&
(
ISBLANK(SELECTEDVALUE('YourTable'[conditions])) ||
CONCATENATEX(SelectedItems, [FilterValue], ",", 'YourTable'[conditions]) = [conditions]
)
)
) > 0,
1,
0
)You can then use this measure as a visual filter or conditional format in your visuals to highlight or display only the relevant rows
Hope this measure filtered values ensures the table filters correctly based
Hello,
reate a calculated column that identifies the rows based on your filtering logic
FilterFlag =
VAR SelectedValues = {"test", "glucubers", "customers", "prueba", "colab", "hyper"}
VAR ConditionLowerCase = LOWER('YourTable'[conditions])
RETURN
IF(
COUNTROWS(
FILTER(
SELECTCOLUMNS(
SelectedValues,
"SelectedValue",
[Value]
),
ConditionLowerCase = [SelectedValue] ||
LEFT(ConditionLowerCase, LEN([SelectedValue])) = [SelectedValue]
)
) > 0,
TRUE,
FALSE
)Use the above calculated column FilterFlag i, when you are in use in your visuals.
Apply the FilterFlag column to the visual-level or page-level filters and set it to filter for TRUE. This will ensure only rows that match your criteria are displayed.The slicer based on Filteroption allows users to select the criteriacreate a disconnected table:
Code:
FilterOptions =
DATATABLE(
"FilterValue", STRING,
{
{"test"},
{"glucubers"},
{"customers"},
{"prueba"},
{"colab"},
{"hyper"}
}
)rajendraongole1_0-1724482186690.png
filter the main table based on multiple selections from the slicer, you can create a measure that checks for the presence of each selected value and then filters the main table
FilteredValues =
VAR SelectedItems = VALUES(FilterOptions[FilterValue])
RETURN
IF(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[FilterFlag] = TRUE &&
(
ISBLANK(SELECTEDVALUE('YourTable'[conditions])) ||
CONCATENATEX(SelectedItems, [FilterValue], ",", 'YourTable'[conditions]) = [conditions]
)
)
) > 0,
1,
0
)You can then use this measure as a visual filter or conditional format in your visuals to highlight or display only the relevant rows
rajendraongole1_1-1724482370446.png
Hope this measure filtered values ensures the table filters correctly based
It's work thanks for make my work a lot easier. myfordbenefits
