The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I would like to get the latest not null value of a column with some condition
Here is an example of my database
name | type | execution | result | ticket | date | job_id |
aaa | OOBE | Internal | Pass | 01/01/2000 | 1 | |
bbb | OOBE | Internal | Pass | 01/01/2000 | 1 | |
aaa | OOBE | Internal | Fail | A-503 | 01/02/2000 | 2 |
bbb | OOBE | Internal | Pass | 01/02/2000 | 2 | |
... | ... | ... | ... | ... | ... | .. |
aaa | OOBE | Internal | Fail | 01/05/2000 | 3 | |
bbb | OOBE | Internal | Fail | A-505 | 01/05/2000 | 3 |
aaa | OOBE | Internal | Pass | 01/05/2000 | 4 |
I would like if the test is still fail in the following day, to get the latest ticket for the same name, type and execution.
An exanple of what I expect:
name | type | execution | result | ticket | date | job_id |
aaa | OOBE | Internal | Pass | 01/01/2000 | 1 | |
bbb | OOBE | Internal | Pass | 01/01/2000 | 1 | |
aaa | OOBE | Internal | Fail | A-503 | 01/02/2000 | 2 |
bbb | OOBE | Internal | Pass | 01/02/2000 | 2 | |
aaa | OOBE | Internal | Fail | A-503 | 01/05/2000 | 3 |
bbb | OOBE | Internal | Pass | A-505 | 01/05/2000 | 3 |
aaa | OOBE | Internal | Pass | 01/05/2000 | 4 |
Also, as you can see in the expected result. If the result become "Pass" the ticket should not be added. What it mean for me. Is that I should only get the latest not null value of ticket that was fail. If the latest result was pass. We should not take the ticket in consideration.
I hope my topic is clear. If anyone need more explanation, do not hesitate !
Best regards
Steph
Solved! Go to Solution.
HI @Cydevos ,
Do you mean to summary your records and only show the last records who have a ticket number? If this is a case, you can try to use the following DAX formula to get a summarized table.
Table =
SUMMARIZE (
FILTER ( Table, [Ticket] <> BLANK () ),
[job_id],
[Date],
[Name],
[Type],
[execution],
"ticket", MAX ( Table[ticket] )
)
Notice: Your ticket column seems like text type, it is hard to find out first/last text value, I'd like to suggest extract numeric value to a new column and apply max function on new column to get last value.
Regards,
Xiaoxin Sheng
HI @Cydevos ,
Do you mean to summary your records and only show the last records who have a ticket number? If this is a case, you can try to use the following DAX formula to get a summarized table.
Table =
SUMMARIZE (
FILTER ( Table, [Ticket] <> BLANK () ),
[job_id],
[Date],
[Name],
[Type],
[execution],
"ticket", MAX ( Table[ticket] )
)
Notice: Your ticket column seems like text type, it is hard to find out first/last text value, I'd like to suggest extract numeric value to a new column and apply max function on new column to get last value.
Regards,
Xiaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
135 | |
114 | |
107 | |
71 | |
57 |
User | Count |
---|---|
240 | |
129 | |
121 | |
96 | |
93 |