Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Dax Experts,
I need help in DAX.
I have 2 tables in my data:
Table1 | |||
Parameter1 | Parameter2 | Date1 | Amount |
A | X | 01-01-2019 | 10 |
B | X | 02-01-2019 | 20 |
C | X | 03-01-2019 | 30 |
D | X | 04-01-2019 | 15 |
E | X | 05-01-2019 | 5 |
F | Y | 01-01-2019 | 21 |
G | Y | 02-01-2019 | 30 |
H | Y | 03-01-2019 | 11 |
I | Y | 04-01-2019 | 23 |
J | Y | 05-01-2019 | 54 |
K | Z | 10-11-2018 | 23 |
L | Z | 11-11-2018 | 65 |
M | Z | 12-11-2018 | 12 |
N | Z | 13-11-2018 | 76 |
O | Z | 14-11-2018 | 76 |
Table2 | ||
Parameter1 | Date2 | Units |
A | 01-11-2018 | 1 |
A | 01-12-2018 | 2 |
A | 03-01-2019 | 3 |
A | 05-01-2019 | 4 |
A | 10-01-2019 | 5 |
B | 01-01-2019 | 10 |
B | 02-01-2019 | 11 |
B | 03-01-2019 | 12 |
B | 04-01-2019 | 13 |
C | 01-01-2019 | 14 |
C | 02-01-2019 | 15 |
C | 03-01-2019 | 16 |
C | 04-01-2019 | 17 |
C | 01-01-2019 | 18 |
D | 02-01-2019 | 19 |
Table1 is having relationship with table2 as one to many (one side at table 1) on Parameter 1.
I need to filter table1 as on or after Date1(from table 1) and take corresponding sum(units) for respective parameter1 from table2. But from table 2 the Date2 should be on or before.
For ex: If we select Date1 slicer as:
Date1Slicer(on or after) |
03-01-2019 |
The output should be coming as :
Output | ||
Parameter2 | Amount | Units |
X | 50 | 6 |
Y | 88 | 33 |
Please note that amout is getting summed up as on or after 03-01-2019 but units are getting summed up as on or before 03-01-2019.
Request you to please help here.
Thanks!
Solved! Go to Solution.
Create a common dimestion DATE
Create a common using Parameter1 Parameter2 from table 1
like
Table 3 = distinct(summarize(Table1,table1[Parameter1],table1[Parameter2])
Join to both tables using parameter 1
Value =
var _max = minx(Date,Date[Date])
return
calculate(count(table1[Amount],filter(all(Date),Date[Date]>=_max))
Unit =
var _max = minx(Date,Date[Date])
return
calculate(count(table2[Unit],filter(all(Date),Date[Date]<=_max))
Appreciate your Kudos.
Create a common dimestion DATE
Create a common using Parameter1 Parameter2 from table 1
like
Table 3 = distinct(summarize(Table1,table1[Parameter1],table1[Parameter2])
Join to both tables using parameter 1
Value =
var _max = minx(Date,Date[Date])
return
calculate(count(table1[Amount],filter(all(Date),Date[Date]>=_max))
Unit =
var _max = minx(Date,Date[Date])
return
calculate(count(table2[Unit],filter(all(Date),Date[Date]<=_max))
Appreciate your Kudos.
Can you explain the logic of where the unit values 6 and 33 are coming from? I am not clear on this. Just in plain language.
Hello Greg,
Sure 🙂
Units is coming from Table2. Sum of units where Date2 is less then or equal to 03-01-2019.
Amount is coming from Table1. Sum of Amount where Date1 is more then or equal to 03-01-2019.
We have Date1 as a slicer as on or before in the report.
I hope I am clear enough now 🙂
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |