cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Klaus Frequent Visitor
Frequent Visitor

Dynamic filtering of rows using a parameter

Hello,

 

I have a table with two attributes: [order_dates] and [sales]. I want to filter rows fitting the following condition:

number of days between MAX(Order_Date) and order_date >= X.

X should be selected by the user through a parameter. The parameter must be visible on the report page.

That means, a query parameter cannot be used

 

I created a parameter table, which holds all possible values for X.

Then I created a calculated column as follows

Flag =
IF (
    DATEDIFF ( 'data'[order_date]; [Latest_Order_Date]; DAY )
        >= SELECTEDVALUE ( 'ParameterTable'[Value] );
    "INCLUDE";
    "EXCLUDE"
)

 

Latest_Order_Date is defined as follows:

Latest_Order_Date =
MAXX (
    ALL ( 'data' );
    'data'[order_date]
)

 

Unfortunately this does not work. Whatever value I select in the parameter table has absolutely no effect. What I am doing wrong?

 

 

 

Thanks a lot in advance,

 

Klaus

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
BILASolution Established Member
Established Member

Re: Dynamic filtering of rows using a parameter

Hi @Klaus

 

You can try this...

 

1. Create a new calculated column (days)

 

days = DATEDIFF(Sales[OrderDate];MAX(Sales[OrderDate]);DAY) 

 

1.png

 

 

2. Finally, Use it as an slicer. Look the picture below.

 

2.png

 

NOTE: The right side of the slicer must be in the right limit

 

I hope this helps

Regards

Klaus Frequent Visitor
Frequent Visitor

Re: Dynamic filtering of rows using a parameter

Thank you a lot!

 

You are right. In this case a parameter is absolutely not necessary.

 

Your suggestion works perfectly.

 

Kindest regards

2 REPLIES 2
BILASolution Established Member
Established Member

Re: Dynamic filtering of rows using a parameter

Hi @Klaus

 

You can try this...

 

1. Create a new calculated column (days)

 

days = DATEDIFF(Sales[OrderDate];MAX(Sales[OrderDate]);DAY) 

 

1.png

 

 

2. Finally, Use it as an slicer. Look the picture below.

 

2.png

 

NOTE: The right side of the slicer must be in the right limit

 

I hope this helps

Regards

Klaus Frequent Visitor
Frequent Visitor

Re: Dynamic filtering of rows using a parameter

Thank you a lot!

 

You are right. In this case a parameter is absolutely not necessary.

 

Your suggestion works perfectly.

 

Kindest regards