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.
Hi
I have below tables and i need to get "Default/Bonus" (red colour columns) column on Trans table. Also mentioned the logic for the columns.
We will be using RLS to filter the student.
Hi,
Create this calculated column formula in the Trans Table
=IF(CALCULATE(COUNTROWS(Score),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime])))>0,CALCULATE(SUM(Score[Bonus]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))),CALCULATE(SUM(Score[Default]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))))
Hope this helps.
It is not giving the expected result.
Score table's year and Trans table's year should be considered and the value is based on the student logs in. if student B logsin then subject S2 only considered on the Trans table new columns.
Your script showing below result.
Hi,
Share data from both tables such that i can paste those in an Excel file. Also, show the exact result you are expecting.
Hi
Table: Subject
Subject | |
Student | Subject |
A | S1 |
B | S2 |
C | S1 |
Score | |||||
StartDate | EndDate | Year | Default | Bonus | Subject |
3/1/2019 1:00 | 3/3/2019 4:00 | 2019 | 5 | 6 | S1 |
2/1/2018 1:00 | 4/4/2018 4:00 | 2018 | 11 | 10 | S1 |
4/1/2019 1:00 | 4/3/2019 4:00 | 2019 | 4 | 2 | S2 |
2/1/2018 1:00 | 3/6/2018 4:00 | 2018 | 12 | 13 | S2 |
ID | CourseDateTime |
1 | 3/1/2019 2:00 |
2 | 3/1/2019 3:00 |
1 | 3/3/2019 3:00 |
2 | 3/4/2019 3:00 |
3 | 3/4/2019 0:00 |
4 | 3/4/2018 0:00 |
5 | 1/1/2018 1:00 |
If Student A logs in the below result.
Expected result is as like below.
ID | CourseDateTime | Default/Bonus | CourseDateTime + (Default/Bonus) |
1 | 3/1/2019 2:00 | 6 | 3/1/2019 8:00 |
2 | 3/1/2019 3:00 | 6 | 3/1/2019 9:00 |
1 | 3/3/2019 3:00 | 6 | 3/1/2019 9:00 |
2 | 3/4/2019 3:00 | 5 | 3/4/2019 9:00 |
3 | 3/4/2019 0:00 | 5 | 3/4/2019 5:00 |
4 | 3/4/2018 0:00 | 10 | 3/4/2018 10:00 |
5 | 1/1/2018 1:00 | 11 | 1/1/2018 0:00 |
If Student B logs in
ID | CourseDateTime | Default/Bonus | CourseDateTime + (Default/Bonus) |
1 | 3/1/2019 2:00 | 4 | 3/1/2019 6:00 |
2 | 3/1/2019 3:00 | 4 | 3/1/2019 7:00 |
1 | 3/3/2019 3:00 | 4 | 3/1/2019 7:00 |
2 | 3/4/2019 3:00 | 4 | 3/4/2019 7:00 |
3 | 3/4/2019 0:00 | 4 | 3/4/2019 4:00 |
4 | 3/4/2018 0:00 | 13 | 3/4/2018 13:00 |
5 | 1/1/2018 1:00 | 12 | 1/1/2018 13:00 |
The logic for the Default /Bonus column should be
if(CourseDateTime>Score.StartDate
&& CourseDateTime<Score.EndDate, Bonus, Default)
Hi,
Are the dates in dd/mm/yyyy or mm/dd/yyyy format?
Hi
dates are in mm/dd/yyyy format.
Thanks
Hi,
In the third table, there is no Student/Subject column. So then how would we know what default/bonus column rule to apply.
Hi
Third tables datetime column should compare with score table.
For example, if third table's datetime value is between the score.StartDate and Score.EndDate then Bonus value else default value. It also should consider the year of third table and year of score table.
condition - if(CourseDateTime>Score.StartDate
&& CourseDateTime<Score.EndDate, Bonus, Default)
Can anyone achieve this?
Create a measure in Trans and copy paste following dax:
It is not giving the expected result.
Score table's year and Trans table's year should be considered and the value is based on the student logs in. if student B logs in then subject S2 only considered on the Trans table new columns.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |