Forum Discussion

Boycie92's avatar
Boycie92
Resolver 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...
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.

v-xjiin-msft's avatar
v-xjiin-msft
Solution 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.

Jon928's avatar
Jon928
Regular Visitor
7 years ago

Thanks  Xi Jin

I tried the code and I am getting the error "Function 'FILTER' is not supported in this context in DirectQuery mode."?