Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am creating a power bi dashboard for machines shutdown planning. Let's say I have 5 machines. Each machine undergoes one or two maintenances every year. During each maintenance period, capacity of a machine is "0". Till a machine undergoes first maintenance, it's capacity is "1". After first maintenance, the capacity is "2" and after second maitenance, the capacity is "3".
Basically, I need something similar to 3D lookup where it checks if on selected date, if the machine is with what capacity i.e. 0/1/2/3 and return that value.
I have 3 tables in my dashboard. One is list of machines, the other is date and third one is machine maitenace schedule as given below. I have done this in excel with nesting of multiple "IF" function but strugging to develop power bi DAX for the same.
Machine | Maint 1 start date | Maint 1 end date | Maint 2 start date | Maint 2 end date |
Machine 1 | 22-Sep-20 | 2-Oct-20 | 16-Nov-20 | 26-Nov-20 |
Machine 2 | 3-Oct-20 | 13-Oct-20 | 27-Nov-20 | 7-Dec-20 |
Machine 3 | 14-Oct-20 | 24-Oct-20 | 8-Dec-20 | 18-Dec-20 |
Machine 4 | 25-Oct-20 | 4-Nov-20 | 19-Dec-20 | 29-Dec-20 |
Machine 5 | 5-Nov-20 | 15-Nov-20 | 30-Dec-20 | 9-Jan-21 |
@ links to members
Solved! Go to Solution.
Hello @Anil59060
You may want to display values in this calculation logic:
If DATE is not on the SD date, display the values in the capitalization table (specific machine).
If Date in SD DATE, it displays 0.
I think you can test the IF function to achieve your goal.
I build a table like yours to test me.
Capitalization table:
Tabla SD:
Create a date table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",FORMAT([Date],"MMM"))
Measure:
Machine 1 =
VAR _SD_Start =
CALCULATE ( MAX ( SD[SD Start] ), FILTER ( SD, SD[Machine] = "Machine 1" ) )
VAR _SD_End =
CALCULATE ( MAX ( SD[ SD End] ), FILTER ( SD, SD[Machine] = "Machine 1" ) )
VAR _Value =
CALCULATE ( SUM ( Cap[Machine 1] ), FILTER ( Cap, Cap[Month] = 'Date'[Month] ) )
RETURN
IF ( 'Date'[Date] >= _SD_Start && 'Date'[Date] <= _SD_End, 0, _Value )
Result:
Other measurements of the machine are the same, you just need to change the three measurements in var.
You can download the pbix file from this link: Return a value if the selected date is between two dates
Best regards
Rico Zhou
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Your table is needlessly complex. Transform it like this
Machine | Maint start date | Maint end date |
Machine 1 | 22-Sep-20 | 2-Oct-20 |
Machine 2 | 3-Oct-20 | 13-Oct-20 |
Machine 3 | 14-Oct-20 | 24-Oct-20 |
Machine 4 | 25-Oct-20 | 4-Nov-20 |
Machine 5 | 5-Nov-20 | 15-Nov-20 |
Machine 1 | 16-Nov-20 | 26-Nov-20 |
Machine 2 | 27-Nov-20 | 7-Dec-20 |
Machine 3 | 8-Dec-20 | 18-Dec-20 |
Machine 4 | 19-Dec-20 | 29-Dec-20 |
Machine 5 | 30-Dec-20 | 9-Jan-21 |
Then you can use a calculated column or measure (depending on how you want to filter) to identify for each day which machines are in maintenance on that day etc.
Thanks for reply. If you could help me with writing DAX for a calculated column to return capacity for selected machine if selected date is between SD start date and SD end date.
Machine | SD Type | SD Start | SD End | Capacity |
Machine 1 | PMI | 22-Sep-20 | 2-Oct-20 | 0 |
Machine 2 | PMI | 3-Oct-20 | 13-Oct-20 | 0 |
Machine 3 | PMI | 14-Oct-20 | 24-Oct-20 | 0 |
Machine 4 | PMI | 25-Oct-20 | 4-Nov-20 | 0 |
Machine 5 | PMI | 5-Nov-20 | 15-Nov-20 | 0 |
Does your capacity counter always reset to zero on the first of january?
Capacity of a machine is "0" when the machine is under maintenance i.e. between SD start date and SD end date. Machine capacity would be 30 when it is not under maintenance.
Thanks,
Anil
that conflicts with your initial statement. Please show expected outcome for a couple of dates around your sample data.
Hi !
Please find details. I have a month wise Machine Capacity table. If a machine is running I get this output from each machine.
Each machine has a maintenance plan as given below. Machine capacity is Zero during maintenance.
Hence in a 3rd table I wanna see available capacity for each date and for that I have put below DAX: See a DAX for Machine 2
Hello @Anil59060
You may want to display values in this calculation logic:
If DATE is not on the SD date, display the values in the capitalization table (specific machine).
If Date in SD DATE, it displays 0.
I think you can test the IF function to achieve your goal.
I build a table like yours to test me.
Capitalization table:
Tabla SD:
Create a date table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",FORMAT([Date],"MMM"))
Measure:
Machine 1 =
VAR _SD_Start =
CALCULATE ( MAX ( SD[SD Start] ), FILTER ( SD, SD[Machine] = "Machine 1" ) )
VAR _SD_End =
CALCULATE ( MAX ( SD[ SD End] ), FILTER ( SD, SD[Machine] = "Machine 1" ) )
VAR _Value =
CALCULATE ( SUM ( Cap[Machine 1] ), FILTER ( Cap, Cap[Month] = 'Date'[Month] ) )
RETURN
IF ( 'Date'[Date] >= _SD_Start && 'Date'[Date] <= _SD_End, 0, _Value )
Result:
Other measurements of the machine are the same, you just need to change the three measurements in var.
You can download the pbix file from this link: Return a value if the selected date is between two dates
Best regards
Rico Zhou
If this post helps,then consider Accepting it as the solution to help other members find it faster.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |