Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
[your measure] =
SUMX(
addcolumns(
summarize(
T,
T[m_id],
T[p_id]
),
"@max",
CALCULATE(
MAX( T[Value] )
)
),
[@max]
)
Best
D
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)
I dont missing relationship. Hopefully you will be right , and it make me happy 🙂
I am looking forward to your answer 🙂
Hi @Anonymous ,
Can you pls confirm if this is the expected output ?
Regards,
Harsh
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 |
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |