Forum Discussion

Boycie92's avatar
Boycie92
Icon for Resolver I rankResolver I
8 years ago
Solved

Direct Query First and Last Dates

Hi

 

I’m wondering if someone can help,

 

I posted a topic a while ago about how to identify the First and Last Dates (link for further details):

 

http://community.powerbi.com/t5/Desktop/First-and-Last-Date-a-product-was-bought/td-p/274495

 

I was given an excellent solution:

 

Identifier =

VAR FirstTransactionDate =

   CALCULATE (

       FIRSTDATE ( Table1[Transaction Date] ),

       FILTER ( Table1, Table1[Product ID] = EARLIER ( Table1[Product ID] ) )

   )

VAR LastTransactionDate =

   CALCULATE (

       LASTDATE ( Table1[Transaction Date] ),

       FILTER ( Table1, Table1[Product ID] = EARLIER ( Table1[Product ID] ) )

   )

RETURN

   SWITCH (

       TRUE (),

       Table1[Transaction Date] = FirstTransactionDate, "First",

       Table1[Transaction Date] = LastTransactionDate, "Last",

       BLANK ()

    )

 

However I am trying to recreate this for a Direct Query Model and I am having a bit of an issue. As it doesn’t seem to support EARLIER, CALCULATE, LASTDATE and FILTER used in a calculated column.

 

Can anyone find a work around for this?

 

please note I do not want to see the first or last dates but text saying "First" and "Last"

 

Thanks

Boycie92

Need Help
Show and Tell
Tips and Tricks
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Boycie92

     

    Then we need to create another new measure to calculate the count of productId.

     

    Count ProductId =
CALCULATE (
    COUNTROWS ( PowerBiTable1 ),
    FILTER (
        ALL ( PowerBiTable1 ),
        PowerBiTable1[ProductId] = MAX ( PowerBiTable1[ProductId] )
    )
)

    And the final measure will be:

     

    Identifier = 
IF (
    PowerBiTable1[Count ProductId] = 1,
    "Unique",
    IF (
        MIN ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[FirstTransactionDate],
        "First",
        IF (
            MAX ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[LastTransactionDate],
            "Last",
            BLANK ()
        )
    )
)

    Thanks,
    Xi Jin.

8 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Boycie92

     

    Yes, it is not possible to use EARLIER, CALCULATE, LASTDATE and FILTER used in a calculated column. There exists a lot of limitations while using Direct Query as data source.

    Please refer: DirectQuery Modelling limitations

     

    Then to achieve your requirement, you should do some workarounds. Please refer to following steps:

     

    1. Create two Measures to calculate the FirstTransactionDate and LastTransactionDate:

    FirstTransactionDate =
CALCULATE (
    MIN ( PowerBiTable1[TransactionDate] ),
    FILTER (
        ALL ( PowerBiTable1 ),
        PowerBiTable1[ProductId] = MAX ( PowerBiTable1[ProductId] )
    )
)
    LastTransactionDate =
CALCULATE (
    MAX ( PowerBiTable1[TransactionDate] ),
    FILTER (
        ALL ( PowerBiTable1 ),
        PowerBiTable1[ProductId] = MAX ( PowerBiTable1[ProductId] )
    )
)

     2. Create a Measure to get the identifier:

    Identifier =
IF (
    MIN ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[FirstTransactionDate],
    "First",
    IF (
        MAX ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[LastTransactionDate],
        "Last",
        BLANK ()
    )
)

     

    Thanks,
    Xi Jin.

    • Boycie92's avatar
      Boycie92
      Icon for Resolver I rankResolver I
      8 years ago

      Hi v-xjiin-msft

       Thank you so much for getting back to me.

      Your solution works perfectly. However I have encountered an issue, that I hope you can help me with.

      I never realised that I was excluding products that has only been sold once.

      Can we edit the measures to say if the product ID appears once then its “Unique” if it appears Multiple times the first transaction (based on date) should say “first” and the last transaction (based on date) should say “Last”

      Thanks in advance,

      Boycie92

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        Boycie92

         

        Then we need to create another new measure to calculate the count of productId.

         

        Count ProductId =
CALCULATE (
    COUNTROWS ( PowerBiTable1 ),
    FILTER (
        ALL ( PowerBiTable1 ),
        PowerBiTable1[ProductId] = MAX ( PowerBiTable1[ProductId] )
    )
)

        And the final measure will be:

         

        Identifier = 
IF (
    PowerBiTable1[Count ProductId] = 1,
    "Unique",
    IF (
        MIN ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[FirstTransactionDate],
        "First",
        IF (
            MAX ( PowerBiTable1[TransactionDate] ) = PowerBiTable1[LastTransactionDate],
            "Last",
            BLANK ()
        )
    )
)

        Thanks,
        Xi Jin.