Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
froxas
Helper II
Helper II

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?

 

Annotation 2020-01-31 185355.png

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@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.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@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.

amitchandak
Super User
Super User

Refer if this blogpost can help

HR-Analytics-Active-Employee-Hire-and-Termination-trend

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.