Forum Discussion
DAX If Statement
Hi can I get some help please.
I want to write an if statement so that if the shipping status is Complete then it only shows the most recent record based on the shipping date. This means that the shipping date of 08/02/2024 will be the record to show. I'm not sure if I need to group by the RefNumber:
| ShippingDate | DateCustomerAdded | InvoiceRecvdDate | Shipping Status | RefNumber |
| 09/04/2024 | 30/04/2024 | 30/04/2024 | Active | 23118616 |
| 15/09/2023 | 19/09/2023 | 19/09/2023 | Complete | 23109497 |
| 17/10/2023 | 19/09/2023 | 20/10/2023 | Complete | 23109497 |
| 08/02/2024 | 19/09/2023 | 14/02/2024 | Complete | 23109497 |
KR,
Polar
Hi Polar_A ,
If you want to add a new column to mark the data you need, then try the following expression.
Column = VAR MaxShipDate = CALCULATE(MAX('Table'[ShippingDate]),ALLEXCEPT('Table','Table'[RefNumber])) RETURN IF('Table'[Shipping Status]="Complete" && 'Table'[ShippingDate]=MaxShipDate,1)If you want to create a new table, then try the following table expression.
Table 2 = FILTER( 'Table', 'Table'[Shipping Status]="Complete" && 'Table'[ShippingDate]=CALCULATE(MAX('Table'[ShippingDate]),ALLEXCEPT('Table','Table'[RefNumber])) )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Polar_A - You can do this with a measure by using the following DAX:
Show Record = VAR _ref = SELECTEDVALUE ( 'Table (4)'[RefNumber] ) VAR _ship_status = SELECTEDVALUE ( 'Table (4)'[Shipping Status] ) VAR _ship_date = SELECTEDVALUE ( 'Table (4)'[ShippingDate] ) VAR calc = CALCULATE ( MAX ( 'Table (4)'[ShippingDate] ), FILTER ( ALL ( 'Table (4)' ), 'Table (4)'[RefNumber] = _ref ) ) RETURN SWITCH ( TRUE (), _ship_status <> "Complete", 1, IF ( CALCULATE ( calc, 'Table (4)'[RefNumber] = _ref ) = _ship_date, 1, 0 ) )You can then add the measure as a filter to the visual and set the value to 1, please see the screenshot below (you do not need the measure in the table, you can just add it to the filter pane):
If this works for you, please accept it as the solution.
Hi Polar_A
There are many ways to achive your required output, and one of them is as follows:
I attach an example pbix file.
Best regards,
5 Replies
Hi Polar_A ,
If you want to add a new column to mark the data you need, then try the following expression.
Column = VAR MaxShipDate = CALCULATE(MAX('Table'[ShippingDate]),ALLEXCEPT('Table','Table'[RefNumber])) RETURN IF('Table'[Shipping Status]="Complete" && 'Table'[ShippingDate]=MaxShipDate,1)If you want to create a new table, then try the following table expression.
Table 2 = FILTER( 'Table', 'Table'[Shipping Status]="Complete" && 'Table'[ShippingDate]=CALCULATE(MAX('Table'[ShippingDate]),ALLEXCEPT('Table','Table'[RefNumber])) )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Hello xifeng_L I am new to PowerBI, I have this Data table and I wanted get an output shown in the picture. There should be one category per workpack and the date will be just the latest date of the category.
Hi Asrquareddd_11 , Your question is unrelated to the topic of this post. You can create a new post to describe your problem and provide sample data (not screenshots).
Polar_A - You can do this with a measure by using the following DAX:
Show Record = VAR _ref = SELECTEDVALUE ( 'Table (4)'[RefNumber] ) VAR _ship_status = SELECTEDVALUE ( 'Table (4)'[Shipping Status] ) VAR _ship_date = SELECTEDVALUE ( 'Table (4)'[ShippingDate] ) VAR calc = CALCULATE ( MAX ( 'Table (4)'[ShippingDate] ), FILTER ( ALL ( 'Table (4)' ), 'Table (4)'[RefNumber] = _ref ) ) RETURN SWITCH ( TRUE (), _ship_status <> "Complete", 1, IF ( CALCULATE ( calc, 'Table (4)'[RefNumber] = _ref ) = _ship_date, 1, 0 ) )You can then add the measure as a filter to the visual and set the value to 1, please see the screenshot below (you do not need the measure in the table, you can just add it to the filter pane):
If this works for you, please accept it as the solution.
Hi Polar_A
There are many ways to achive your required output, and one of them is as follows:
I attach an example pbix file.
Best regards,
