Forum Discussion

froxas's avatar
froxas
Helper II
6 years ago
Solved

Dynamic filter on two variable

Hi,

I have situation what I do not know how to solve, maybe somebody could bring me on a way.

 

I have a table with a contracts: contract number, version, start date and and date.

User needs to get a report which satisfies all below conditions:

1. report should be made on users selected date -> start_date should be before report date

2. report needs to show last version of contract

3. report needs to show only contracts with a status ACTIVE

 

 

the main issue is that contract status needs to be calculated like below:

IF report_date (user selected date) is between start_date AND termination_date THEN Status = ACTIVE

 

It means the status depends on a values of filter. Logically there needs to be double filtering.

 

How it can be atchieved? maybe through some temporary table?

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    froxas ,

     

    I would suggest you create a calculate column using dax below and then you can filter other columns based on that column.

    Status =
VAR report_date =
    SELECTEDVALUE ( Calendar[Date] )
RETURN
    IF (
        report_date >= start_date
            && report_date <= termination_date,
        "Active",
        "Inactive"
    )

    Community Support Team _ Jimmy Tao

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

2 Replies