Forum Discussion
Advanced filtering with User-Input
Hi,
for an user requirment I need to filter a custom visual (Acterys Matrix). The filter has to check if:
- Field status is not "ACT" and a Date selected by a User is larger or equal than the field ReportDate
- or the status is not "FCT" and a Date selected by a user is smaller then the field ReportDate
In SQL the Query would transfer to:
WHERE (ReportDate >= '2021-04-30' AND status <> 'ACT') or (ReportDate < '2021-04-30' AND status <> 'FCT')
In the Filter Pane it seems like I can not add different fields to the same filter - And it would be convienent that the date could be selected from a drop down list.
As the Visual provides Write-Back functionality I have to work with the original query table- meaning I can only filter the data here and not with an "output"-table that is filtered by a look-up table...
What I tried so far was passing Parameters to the query which works fine . However, the End-User uses PowerBI web-service and I haven't found how a web-user can change/manage the parameters in the web version.
Any help apppreciated!
Max
Hi,
I found a solution to my problem.
- Create Table/Query from what users can select the date - do not build a relationship to the measure table or other dimension tables - date /filter field needs to be the same data type /format like in measurement table!
- Put filter selection field into a slicer visual
- In the fact table create a selectDate measurement using "selectDate = SELECTEDVALUE(filterDates[filterNum])
- Create another measurement that references the measurement "selectDate"
AmountSelect =
if(
OR(
OR(
OR(
AND(
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name])= "ACT",
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenZeitDimension'), "yearmon", [yearmon]) <= [selectDate]),
AND(
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "FCT", [Name])= "FCT",
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenZeitDimension'), "yearmon", [yearmon]) > [selectDate])),
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name]) = "SomeOtherCondtion"),
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name])= "SomeOtherCondition2"),
SUM('OLAP CUBE'[Amount])
)
2 Replies
Hi,
I found a solution to my problem.
- Create Table/Query from what users can select the date - do not build a relationship to the measure table or other dimension tables - date /filter field needs to be the same data type /format like in measurement table!
- Put filter selection field into a slicer visual
- In the fact table create a selectDate measurement using "selectDate = SELECTEDVALUE(filterDates[filterNum])
- Create another measurement that references the measurement "selectDate"
AmountSelect =
if(
OR(
OR(
OR(
AND(
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name])= "ACT",
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenZeitDimension'), "yearmon", [yearmon]) <= [selectDate]),
AND(
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "FCT", [Name])= "FCT",
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenZeitDimension'), "yearmon", [yearmon]) > [selectDate])),
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name]) = "SomeOtherCondtion"),
SELECTCOLUMNS(RELATEDTABLE('OLAP KennzahlenDimension'), "ACT", [Name])= "SomeOtherCondition2"),
SUM('OLAP CUBE'[Amount])
)Hi, Anonymous
Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread?
Other community members will easily find the solution when they get the same issue.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
