Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a situation like on the picture (WHAT I HAVE)
So multiple values in each column. I need to find last address which was entered (last entry no.)
When I type formula
Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address" && max(Table1[Entry_No]))) |
I got the red table which is not correct, and what I need is in the green table.
I tried with calculated table, but the measure will be also fine, just to get last value based on type address and last entry no.
Thanks,
Renato.
Solved! Go to Solution.
Hi @renatof,
If you only to get the max Entry_No when the type is Address, please try this formula again.
Table 3 = TOPN ( 1, SUMMARIZE ( 'Table1', Table1[Person_No], Table1[Value], 'Table1'[Type], "max", CALCULATE ( MAX ( 'Table1'[Entry_No] ), FILTER ( 'Table1', 'Table1'[Type] = "Address" ) ) ), [max], DESC )
Best Regards,
Cherry
Hi @renatof,
By my test, you could have a try with this formula below.
Table 2 =
TOPN (
CALCULATE (
COUNT ( 'Table1'[Person_No] ),
FILTER ( 'Table1', 'Table1'[Entry_No] = MAX ( 'Table1'[Entry_No] ) )
),
'Table1',
'Table1'[Entry_No], DESC
)
Then you will get the output.
Hope this can help you!
Best Regards,
Cherry
Hi,
this could work but column type I have multiple types so one more filter which is related to Table1[Type] = "Address" + last entry no.
Hi @renatof,
If you only to get the max Entry_No when the type is Address, please try this formula again.
Table 3 = TOPN ( 1, SUMMARIZE ( 'Table1', Table1[Person_No], Table1[Value], 'Table1'[Type], "max", CALCULATE ( MAX ( 'Table1'[Entry_No] ), FILTER ( 'Table1', 'Table1'[Type] = "Address" ) ) ), [max], DESC )
Best Regards,
Cherry
Thank you. This is solution 🙂
Why don't you use the visual level filter? Select show only TOP N category and sort it on [Entory_No]
Have you tried
Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address" ), Table1.Entry_No = max(Table1[Entry_No])) |
Basically i split the two fliters. Without test data i cant simulate to be certain.
Yes I tried with Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address");Table1[Entry_No] = MAX(Table1[Entry_No]))
and I got error A Function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |