Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SUM of Max values

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_idp_idvaluedate
123102020-01-10
123202020-01-11
223502020-01-10
4123102020-01-15
223102020-01-05
7 REPLIES 7
Anonymous
Not applicable

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

 

Best

D

Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

Best
D
Anonymous
Not applicable

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

I am looking forward to your answer 🙂

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Can you pls confirm if this is the expected output ?

image.PNG

Regards,

Harsh

 

Anonymous
Not applicable

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_idp_idvaluedate
123102020-01-10
123202020-01-11
223502020-01-10
4123102020-01-15
223102020-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_idp_idvaluedate
123122020-01-10
123202020-01-11
223502020-01-15
4123102020-01-09
223102020-01-05
4123302020-01-10
33052020-01-09

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors