Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
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
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.
Thanks Xi Jin
I tried the code and I am getting the error "Function 'FILTER' is not supported in this context in DirectQuery mode."?
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
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |