The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi,
I am quite new to PowerBi and I have been trying to solve this myself without any luck.
In the data set I have which has more columns than displayed below we capture when a serial number is repaired with corresponding case number.
It might be that the same serial number & case are worked on multiple dates, which generates multiple lines in the dataset.
I am looking for an option to register a hit if a Serial Number repaired back within 6 months of last repair date with a different case number. The hit would need to be registered at the date it was first repaired again.
Below is a sample of how the data could look like I have added the same Serial Number 5 times with 3 different cases.
In the last column I displayed how the "hit" could be registered.
It might be that I am thinking too complicated and that this is easy to solve.
Hope somebody is able to help me with this
Case Number | Serial Number | Date Repair | Returned in 6 Months |
X12345 | U7650 | 10-5-2019 | 0 |
X12345 | U7650 | 11-5-2019 | 0 |
X12347 | U7652 | 10-6-2019 | 0 |
X12348 | U7653 | 11-6-2019 | 0 |
X12349 | U7654 | 12-6-2019 | 0 |
X12350 | U7655 | 13-6-2019 | 0 |
X12351 | U7656 | 14-6-2019 | 0 |
X12352 | U7657 | 15-6-2019 | 0 |
X12353 | U7650 | 10-7-2019 | 1 |
X12353 | U7650 | 11-7-2019 | 0 |
X12355 | U7660 | 11-7-2019 | 0 |
X12356 | U7650 | 11-7-2020 | 0 |
Solved! Go to Solution.
It looks like he just forgot a CALCULATE/FILTER syntax. I've fixed your query to (hopefully) be free of syntax errors.
Column =
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
CALCULATE(
Countrows('repairs (2)');
FILTER( ALL('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber]) &&
[CASENUMBER] <> case6 &&
[DateOfRepair] < date_ &&
[DateOfRepair] >= c6_mon
)
) > 0 ; 1 ; 0 )
Column = date_ = [Date repair] VAR 6_mon = Date(YEAR(date_) , MONTH(Date_)-6 , DAY(date_))
VAR case = [Case number] return IF(
Calculate( Countrows('table'), all(table), [serial number] = earlier([serial number]) ,
[Case number] <> case [Date Repair] < date_ , [Date Repair] >= 6_mon ) > 0 , 1 , 0 )
Hi tex628,
Thank you for your help!
I am getting 2 errors which I need some additional help with
1. For the IF formula that more than 3 arguments are used,
I have noticed that Countrows is seen as Logical test and All is seen as value if true, resulting in Serial Number being seen as value if false
2. In the EARLIEST[SERIALNR] it is stating that parameter is not the correct type, can this because the column is stored as text?
it is stored as text due to the fact that the serial number contains letters too
Below is how the DAX is looking for me now:
Column =
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
Countrows('repairs (2)');
all('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber] ;
[CASENUMBER] <> case6
[DateOfRepair] < date_ ;
[DateOfRepair] >= c6_mon
) > 0 ; 1 ; 0 )
It looks like he just forgot a CALCULATE/FILTER syntax. I've fixed your query to (hopefully) be free of syntax errors.
Column =
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
CALCULATE(
Countrows('repairs (2)');
FILTER( ALL('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber]) &&
[CASENUMBER] <> case6 &&
[DateOfRepair] < date_ &&
[DateOfRepair] >= c6_mon
)
) > 0 ; 1 ; 0 )
@Cmcmahan @tex628 @mochabits
Thanks to all of you for helping me out on this!
It works like a charm now!
Hahaha missing CALCULATE() indeed! 🙂 Thank you for catching it!
HiDamo92,
Sorry for my bad understanding, I am not clear about your logic for your “Returned in 6 month”. If possible, could you please explain this for me by more detailed sample or information?
What did you mean of “if a Serial Number repaired back within 6 months of last repair date with a different case number. ” and “The hit would need to be registered at the date it was first repaired again. “?
If you want to record serial number which will return back, why the last row not show 1?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |