Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have a table that contains failure information recorded which includes Date_Failure_End(which is the date the failure item was fixed) and an Date_Failure_Start(date when the failure item started)
I also have a Date table that just has date items from 01-01-2006 till Current date.
Both of these tables are linked and the relationship is by the Date_Failure_Start from the Failure Table
i have a Date Slicer filter that i would like to use to filter the Failure Table to only show items where the date is in the range of start_date and end_date and for items that do not have end_dates(or end_date is Null) i would like the range to be based on the current_date
i'm thinking that this could be solved by creating a measure that assigns the value "1" for each item if the slected date is in the range of the start_date and the end_date or in the event where there is no end_date the check is carried out if the selected date is in between the start_date and the current_date. Finally assigns "0" to the other items that do not satisfy the condition
i'm not very good at DAX queries especially around nested if statements , i was wandering if anyone could help
Measure = VAR current_date = MIN( 'Date Table'[Date]) VAR start_date = MIN( 'NPTTest'[DATE_FAILURE_START]) VAR end_date = MIN( 'NPTTest'[DATE_FAILURE_END]) RETURN IF( ( current_date >= start_date ) && ( current_date <= end_date ),1,0 )
Solved! Go to Solution.
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Also, you might find this article of interest:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Also, you might find this article of interest:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
This is great, it look like exactly what i need.
Thanks you so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |