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.

Boycie92's avatar
Boycie92
Resolver I
8 years ago

Hi v-xjiin-msft

 

Thanks for the help.

 

I have having an issue with the Identifier measure.

 

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

 

I get a red line at the bold parts of you formula. I looked at the error message and it says “a single value for column "" cannot be determined”

 

Any ideas why?

 

Thanks,

Boycie92

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

    Boycie92

     

    First, PowerBiTable1 is my sample table name. Please verify that you have replaced it with your own source table.

     

    Then since you are using the Direct Query data mode. So all my sample DAX expressions are used to create measures, not calculated columns. Please verify this.

     

    By the way, if you can share us some screenshots about your report structure like how you define the measures in your scenario. It'll help us more to troubleshoot your issue.

     

    Thanks,
    Xi Jin.

    • Boycie92's avatar
      Boycie92
      Resolver I
      8 years ago

      Hi v-xjiin-msft

       

      It was my fault I was referencing the wrong column.

       

      I have another issue, I wanted to create this (ideally as a column), so I could the create a measure that would not count any rows that did not say “First”. Is there any way I can achieve this since i am now using a measure?

       

      I can use it as a Visual Level filter but i would prefer not to.

       

      Thanks,

      Boycie92

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

        Boycie92 

         

        There exists a lot of limitations when using Direct Query as I said above. One of them is it is not possible to define new calculated columns. Please refer my shared article.

         

        So if you want to create a column. Nope, you can't achieve this.

         

        Thanks,
        Xi Jin.