Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@harshnathani added more details below to main post hope it's clearer
Consider the below table:
id | rag | date |
101 | red | 01/06/2020 |
101 | red | 02/06/2020 |
103 | red | 01/06/2020 |
103 | green | 02/06/2020 |
104 | amber | 02/06/2020 |
103 | green | 05/06/2020 |
101 | green | 05/06/2020 |
I'm looking to create measures:
1- that tallies the count of all id except one submission date prior the latest date (02/06/2020 in this case).
My code attempt for this would be, assuming above table named as table
```
count_prior_latest =
VAR temp_table = Filter(table, table[date] <> max(table[date])) -- remove max date, and create temp table
VAR second_max = max(temp_table[date]) -- second latest entry
Return Calculate(countrows(temp_table[id]) ,
Filter(temp_table, temp_table[date] = second_max)
) -- only count second latest
Desired outcome for 1 in a matrix (id vs rag)
count_pior_latest
red | amber | green | |
101 | 1 | ||
103 | 1 | ||
104 | 1 |
Thank you in advance
Solved! Go to Solution.
Hi @giordafrancis ,
Took me some time. But it was all worth the effort (I hope so 🙂 )
Use this measure
Second Largest =
VAR _max =
CALCULATE (
MAX ( 'Table8'[date] ),
ALL ( 'Table8' )
)
VAR _second =
CALCULATE (
MAX ( 'Table8'[date] ),
FILTER (
ALL ( Table8 ),
Table8[date] < _max
)
)
RETURN
CALCULATE (
COUNT ( 'Table8'[id] ),
FILTER (
'Table8',
'Table8'[date] = _second
)
)
I added some more data to check.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @giordafrancis ,
Took me some time. But it was all worth the effort (I hope so 🙂 )
Use this measure
Second Largest =
VAR _max =
CALCULATE (
MAX ( 'Table8'[date] ),
ALL ( 'Table8' )
)
VAR _second =
CALCULATE (
MAX ( 'Table8'[date] ),
FILTER (
ALL ( Table8 ),
Table8[date] < _max
)
)
RETURN
CALCULATE (
COUNT ( 'Table8'[id] ),
FILTER (
'Table8',
'Table8'[date] = _second
)
)
I added some more data to check.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @giordafrancis ,
Not clear with the expected output, Can you pls explain a little better.
Why is 103 red not counted ?
Regards,
HN
Hi @harshnathani ,
Thank you for your post. the expected output only accounts for counts of id for 02/06. The second latest entry. If a date value of 06/06 was present on the date column the second latest entry would be 05/06.
There's no id, rag pair (103, red) for date 02/06. Hope this is clear, if not let me know 🙂
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |