The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello
I have the data:
A, 01/01/20, X1
A, 02/01/20, X2
A, 01/01/20, X3
A, 02/01/20, X4
B, 01/01/20, X5
B, 02/01/20, X6
B, 01/01/20, X7
...
I want to get the X value which corresponds to the maximum date for each of the A and B.
Any idea please?
Solved! Go to Solution.
Hi @Anonymous ,
Here the sample data:
Then you can use the following measure:
Measure = VAR A = CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1])) RETURN CALCULATE(CONCATENATEX('Table','Table'[Column3],","),'Table'[Column2] =A)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , Try a measure like
calculate(sum(Table[col 3]), filter(Table, Table[Date] = calculate(max(Table[date]), allexcept(Table, Table[col 1]))))
@amitchandak I cannot make any sense of what you wrote, can you explain step by step please?
Hi @Anonymous ,
Here the sample data:
Then you can use the following measure:
Measure = VAR A = CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1])) RETURN CALCULATE(CONCATENATEX('Table','Table'[Column3],","),'Table'[Column2] =A)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , I do not see any column name,
So the columns are col1, date, col3
so you can try max of col3 on max of date on allexpect col 1
calculate(maxTable[col 3]), filter(Table, Table[Date] = calculate(max(Table[date]), allexcept(Table, Table[col 1]))))
User | Count |
---|---|
80 | |
73 | |
39 | |
30 | |
28 |
User | Count |
---|---|
107 | |
99 | |
55 | |
49 | |
46 |