Forum Discussion

leilei787's avatar
leilei787
Icon for Helper II rankHelper II
4 years ago
Solved

Looking for a filter solution

Hello:   i have a question about setting up a filter, wonder if anyone can help! thanks in advance!   i have the following table below. Customer Name, Product and Purchase Date. ( Other columns a...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi leilei787 ,

     

    Firstly, create a Filter table as below.

    Filter = 
{"Active Customer?","90 Days Filter","180 Days Filter"}

    Then create a measure.

    Measure = 
VAR _SELECTION = SELECTEDVALUE('Filter'[Filter])
VAR _CURRENT_DAY = DATE(2022,04,28) /*TODAY()*/ 
VAR _90DAYS_BEFORE = _CURRENT_DAY - 90
VAR _180DAYS_BEFORE = _CURRENT_DAY - 180
RETURN
IF(
MAX('Table'[Product]) = 1001, 
SWITCH(
_SELECTION, 
"Active Customer?",IF(MAX('Table'[Purchase Date])>_90DAYS_BEFORE,"Yes","No"),
"90 Days Filter",IF(MAX('Table'[Purchase Date])<=_90DAYS_BEFORE,"Yes","No"),
"180 Days Filter",IF(MAX('Table'[Purchase Date])<=_180DAYS_BEFORE,"Yes","No")
)
)

    Result is as below.

     

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

leilei787's avatar
leilei787
Icon for Helper II rankHelper II
4 years ago

Hi Amitchandak, thank you!

 

With the solution you provide, it seems we can only setup individual filters instead of one filter with 3 options? ( or in your solution, looks like 4 options)

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi leilei787 ,

 

Firstly, create a Filter table as below.

Filter = 
{"Active Customer?","90 Days Filter","180 Days Filter"}

Then create a measure.

Measure = 
VAR _SELECTION = SELECTEDVALUE('Filter'[Filter])
VAR _CURRENT_DAY = DATE(2022,04,28) /*TODAY()*/ 
VAR _90DAYS_BEFORE = _CURRENT_DAY - 90
VAR _180DAYS_BEFORE = _CURRENT_DAY - 180
RETURN
IF(
MAX('Table'[Product]) = 1001, 
SWITCH(
_SELECTION, 
"Active Customer?",IF(MAX('Table'[Purchase Date])>_90DAYS_BEFORE,"Yes","No"),
"90 Days Filter",IF(MAX('Table'[Purchase Date])<=_90DAYS_BEFORE,"Yes","No"),
"180 Days Filter",IF(MAX('Table'[Purchase Date])<=_180DAYS_BEFORE,"Yes","No")
)
)

Result is as below.

 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.