I am working on PowerBI desktop.
I have a table “DataTable”, with lots of columns, including such columns as “date”, “is_user_valid”, “country”, “value” etc.
In the report, there is a slicer which shows different measures per country, on which many filters to “DataTable” applied to make measures results more specific.In general, there are >30 filters, including filters on “date=01-Jan-2024”, “is_user_valid=yes”, “country” and others.
I need to create a new measure for this slicer, which will be showing SUM(value) for the specified date (01-Feb-2024) for “is_user_valid=no”. It means, that all other slicer filters should be kept for the measure, while those two should be replaced by the values specified directly (Date=01-Feb-2024 and is_user_valid=no).
I found one way to solve the task, but it is too inconvenient:
NewMeasure = Calculate(
sum(value),
ALLEXCEPT('DataTable', 'DataTable'[Country]),
'DataTable'[date] = "01-Feb-2024"
'DataTable'[is_user_valid] = "no"
)
This method is not good because, as I said, slicer has 30+ filters, and here I have to specify all of filtered fields in “ALLEXCEPT” function to keep them.
I’ve tried to solve the problem using ALL (it works, but all filters lost), “REMOVEFILTERS” (applying that just gives me Blank), and ALLSELECTED (also, blank).
Do not know what to try else.