March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello people,
I will show the structure of my table so that you can understand better.
Register | Class | Date |
000 | A | 03/05/2020 |
000 | B | 03/06/2020 |
000 | A | 03/05/2020 |
000 | C | 03/07/2020 |
111 | A | 03/10/2020 |
111 | A | 03/15/2020 |
111 | B | 03/08/2020 |
222 | C | 03/20/2020 |
222 | A | 03/26/2020 |
Register | Class | Date |
000 | C | 03/07/2020 |
111 | A | 03/15/2020 |
222 | A | 03/26/2020 |
Solved! Go to Solution.
Hi @Anonymous ,
I took a look at your .pbix file and the error is reported because your data cannot use the LOOKUPVALUE () function.
I wrote another method again, you try again:
1. Creating measure3:
Measure 3 =
CALCULATE(
MAX(Sheet3[Date]),
ALLEXCEPT(
Sheet3,
Sheet3[Register]
)
)
2. Creating measure4
Measure 4 =
CALCULATE(
COUNT(Sheet3[Register]),
FILTER(
Sheet3,
Sheet3[Date] = [Measure 3]
)
)
3. add the two measures to the visual and add filter on the visual:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this:
Measure =
VAR x =
SUMMARIZE(
Sheet1,
Sheet1[Register],
"Date", MAX(Sheet1[Date])
)
RETURN
COUNTROWS(x)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the answer @v-lionel-msft !
I tried this measure and it really only counts distinct Register for each Date, but the Class column does not show the respective class for that Register and this Max (Date).
It is showing random values in the Class column. That's the only problem with this measure, cause is important for me to capture that exact Class of that exact Register (all of that based on the most recent Date of that Register).
Hi @Anonymous ,
How about this:
// calculated table
Table =
VAR x =
SUMMARIZE(
Sheet1,
Sheet1[Register],
"Date", MAX(Sheet1[Date])
)
RETURN
ADDCOLUMNS(
x,
"Class",
LOOKUPVALUE(
Sheet1[Class],
Sheet1[Date], [Date]
)
)
//measure
Measure 2 = COUNTROWS('Table')
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
When a try to create this new table, an error pops up: "A table with multiple values was provided, with a single value expected"
I will leave a link here for my .pbix file so that you can understand better:
test2.pbix
PS: My table's columns has different names from the example table I posted in my question, but:
Register = COD
Class = LEVEL
Date = DATE
Hi @Anonymous ,
I took a look at your .pbix file and the error is reported because your data cannot use the LOOKUPVALUE () function.
I wrote another method again, you try again:
1. Creating measure3:
Measure 3 =
CALCULATE(
MAX(Sheet3[Date]),
ALLEXCEPT(
Sheet3,
Sheet3[Register]
)
)
2. Creating measure4
Measure 4 =
CALCULATE(
COUNT(Sheet3[Register]),
FILTER(
Sheet3,
Sheet3[Date] = [Measure 3]
)
)
3. add the two measures to the visual and add filter on the visual:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dense rank on opportunity id and date and filter 1.
As Column == RANKX(Sales, Sales[Category] & Sales[Brand], ,DESC,Dense )
Sorry, but I didn't quite understand what you did 😞
Try like
COUNT = COUNTROWS(DISTINCT('Table'[Register]), filter(all('Table'),'Table'[Date] =max('Table'[Date])))
Thanks for the quick response! 🙂
This measure is giving a syntax error right after the Table'[Register])
The error is "unexpected parameter".
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |