SUM of Max values

[Anonymous]

Hi all,
  I want to write a dax measure for following table. I write a sql query that gives the correct result, but I could not convert to dax format. I tried following dax, it does not give correct result.
   By the way, I am working on direct qurey  and want to filter my data according to date via using slicer(between type). Please , pay attention to these conditions when you write a solution.

SQL: 

select sum(total)
from(select p_id,m_id,max(value) as total
from table 
group by p_id,m_id)

DAX:(not correctly result)
SUMX(
SUMMARIZE(table,table[m_id],table[p_id], "Total", MAX(table[value])),[Total])

As you can see, every m_id has p_id and table includes more than one value for different date.
Table:

m_id	p_id	value	date
1	23	10	2020-01-10
1	23	20	2020-01-11
2	23	50	2020-01-10
4	123	10	2020-01-15
2	23	10	2020-01-05

[Anonymous]

[your measure] =
SUMX(
	addcolumns(
		summarize(
			T,
			T[m_id],
			T[p_id]
		),
		"@max",
			CALCULATE(
				MAX( T[Value] )
			)
	),
	[@max]
)

Best

D

[Anonymous]

Hi ,
  thanks for reply. But it is not working very well. When I select p_id with no date filter, it shows correct answer. But it returns wrong answer  when p_id and date filter are chosen. If I share sql query, it can help you about  what I want.

   **Date and p_id selection will be done via filters in power bi.

select sum(total)
from(select p_id,m_id,max(value) as total
from table 
where p_id=23 and date between 2020-01-04 and  2020-01-11
group by p_id,m_id)

[Anonymous]

My measure is OK. You are very likely missing a relationship between a Calendar and the fact table. Note that Best Practices say you should never expose columns of a fact table to users.

I'm going to build a small model to prove/disprove that my measure works correctly. Stay tuned 🙂

Best
D

[Anonymous]

My measure works CORRECTLY. You are using different tables for different examples.

Best
D

[Anonymous]

I dont missing relationship. Hopefully you will be right ,  and  it make me happy 🙂

I am looking forward to your answer 🙂

[harshnathani]

Hi @Anonymous ,

Can you pls confirm if this is the expected output ?

Regards,

Harsh

[Anonymous]

Hello Harsh,
    Thanks for reply. I try to explain with following example.
example 1: I  selected p_id =23 from filter. Correct result must be  70(max value for each m_id)

example 2: If I dont select p_id ,correct result total of value is  80 ( 20+50+10)

m_id	p_id	value	date
1	23	10	2020-01-10
1	23	20	2020-01-11
2	23	50	2020-01-10
4	123	10	2020-01-15
2	23	10	2020-01-05

example 3: This is my case. I want to calculated total value for selected date.  For example I selected  date between 2020-01-04 and  2020-01-11. Result should be 65 . ( by the way, I changed to value and add 1 row on the table to understand the example better. )

m_id	p_id	value	date
1	23	12	2020-01-10
1	23	20	2020-01-11
2	23	50	2020-01-15
4	123	10	2020-01-09
2	23	10	2020-01-05
4	123	30	2020-01-10
3	30	5	2020-01-09