Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Polar_A
Regular Visitor

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:

 

ShippingDateDateCustomerAddedInvoiceRecvdDateShipping StatusRefNumber
09/04/202430/04/202430/04/2024Active23118616
15/09/202319/09/202319/09/2023Complete23109497
17/10/202319/09/202320/10/2023Complete23109497
08/02/202419/09/202314/02/2024Complete23109497

 

KR,

 

Polar

1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

Hi @Polar_A ,

 

If you want to add a new column to mark the data you need, then try the following expression.

 

xifeng_L_0-1715941726297.png

 

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.

 

xifeng_L_1-1715941882392.png

 

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~

 

 

View solution in original post

3 REPLIES 3
DataNinja777
Super User
Super User

Hi @Polar_A 

There are many ways to achive your required output, and one of them is as follows:

DataNinja777_0-1715944533291.png

I attach an example pbix file.  

Best regards,

mark_endicott
Responsive Resident
Responsive Resident

@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):

 

mark_endicott_0-1715944554539.png

 

If this works for you, please accept it as the solution. 

 

 

xifeng_L
Solution Sage
Solution Sage

Hi @Polar_A ,

 

If you want to add a new column to mark the data you need, then try the following expression.

 

xifeng_L_0-1715941726297.png

 

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.

 

xifeng_L_1-1715941882392.png

 

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~

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.