Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
bacuy
New Member

New Table With Unique Value and Recent data

‎02-27-2022 10:01 PM

Hi All,

I have data from multiple sources of Excel files, which the data is updated manually for each column depending on the system cut-off time. some time complete files or only provide certain columns and I am using folder updates to merge and transform.
Its impact on the data that is duplicated for each column.
I am trying to create unique value for each event data from the latest recent data. I expect the most effective way to create the new table since the system is given that make me can not adjust the source 😞

 

Thank you in advance!

Here is the case 

sales table :

event_datesold1sold2paid1paid2update_date
1-Jan-221010551-Jan-22
1-Jan-2211  62-Jan-22
1-Jan-22 127 3-Jan-22
1-Jan-2215   4-Jan-22
1-Jan-2212   5-Jan-22
1-Jan-22   36-Jan-22
2-Jan-221010552-Jan-22
2-Jan-221  13-Jan-22
2-Jan-22   24-Jan-22
1-Jan-221510  5-Jan-22
3-Jan-2230 1 3-Jan-22
3-Jan-22 10 53-Jan-22
3-Jan-22219  4-Jan-22
3-Jan-22210  4-Jan-22


there are some rules applied to the output
1. The unique key is the event_date

2. If the latest update_date give the blank value

    --> use the previous value from the previous update_date

    --> case event_date 1 Jan 2022, latest_update date is 6 Jan. But Only has the paid2 value.

   ---> other column value fulfill from the previous update_date

3. if the latest update_date has duplicate rows then take the highest value.

new-table

Expected output:

event_datesold1sold2paid1paid2update_date
1-Jan-221510736-Jan-22
2-Jan-22110524-Jan-22
3-Jan-22219154-Jan-22
Labels:
Message 1 of 4
414 Views
0
2 ACCEPTED SOLUTIONS
v-jinweili-msft
Microsoft Employee
Microsoft Employee

‎03-02-2022 12:02 AM

Hi @bacuy ,

 

I have two solutions here for your reference,please check.

 

  • Create measures.

 

Measure paid1=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [paid1] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[paid1] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure paid2=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [paid2] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[paid2] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure sold1=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [sold1] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[sold1] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure sold2=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [sold2] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[sold2] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )

 

 

Output:

vjinweilimsft_0-1646205724787.png

 

  • Create a new table.

 

New Table=
ADDCOLUMNS (
    DISTINCT ( 'sales table'[event_date] ),
    "sold1",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [sold1] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[sold1] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "sold2",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [sold2] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[sold2] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "paid1",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [paid1] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[paid1] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "paid2",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [paid2] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[paid2] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "update_date",
        CALCULATE (
            MAX ( 'sales table'[update_date] ),
            ALLEXCEPT ( 'sales table', 'sales table'[event_date] )
        )
)

 

Output:

vjinweilimsft_2-1646208086944.png

 

Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 2 of 4
321 Views
0
bacuy
New Member

‎03-02-2022 12:52 AM

Hi @v-jinweili-msft 
THANK YOU !!!!!

It Works 🙂

View solution in original post

Message 3 of 4
316 Views
0
3 REPLIES 3
bacuy
New Member

‎03-02-2022 01:11 AM

Hi @v-jinweili-msft,
I'm sorry... I Have a problem here... 

When I try to make the total sold from the new table, the measurement is not what I expected.

 

Total Sales = SUMX('New Table', 'New Table'[sold1] +'New Table'[sold2])

bacuy_0-1646212164443.pngkindly need your help

 



Message 4 of 4
311 Views
0
bacuy
New Member

‎03-02-2022 12:52 AM

Hi @v-jinweili-msft 
THANK YOU !!!!!

It Works 🙂

Message 3 of 4
317 Views
0
v-jinweili-msft
Microsoft Employee
Microsoft Employee

‎03-02-2022 12:02 AM

Hi @bacuy ,

 

I have two solutions here for your reference,please check.

 

  • Create measures.

 

Measure paid1=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [paid1] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[paid1] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure paid2=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [paid2] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[paid2] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure sold1=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [sold1] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[sold1] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )
Measure sold2=
VAR _date =
    CALCULATE (
        LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
        FILTER (
            'sales table',
            [event_date] = MAX ( 'sales table'[event_date] )
                && [sold2] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'sales table'[sold2] ),
        FILTER (
            'sales table',
            'sales table'[update_date] = _date
                && 'sales table'[event_date] = MAX ( 'sales table'[event_date] )
        )
    )

 

 

Output:

vjinweilimsft_0-1646205724787.png

 

  • Create a new table.

 

New Table=
ADDCOLUMNS (
    DISTINCT ( 'sales table'[event_date] ),
    "sold1",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [sold1] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[sold1] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "sold2",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [sold2] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[sold2] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "paid1",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [paid1] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[paid1] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "paid2",
        VAR _date =
            CALCULATE (
                LASTNONBLANK ( 'sales table'[update_date], TRUE () ),
                FILTER (
                    'sales table',
                    [event_date] = EARLIER ( [event_date] )
                        && [paid2] <> BLANK ()
                )
            )
        RETURN
            CALCULATE (
                MAX ( 'sales table'[paid2] ),
                FILTER (
                    'sales table',
                    'sales table'[event_date] = EARLIER ( [event_date] )
                        && 'sales table'[update_date] = _date
                )
            ),
    "update_date",
        CALCULATE (
            MAX ( 'sales table'[update_date] ),
            ALLEXCEPT ( 'sales table', 'sales table'[event_date] )
        )
)

 

Output:

vjinweilimsft_2-1646208086944.png

 

Best Regards,
Jinwei Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 2 of 4
322 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All