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 am a newbie to DAX.
I have 4 tables in PowerBI, say T1, T2, T3, T4 with the following relationship:
T1 (*) ---> (1) T2 (1) ---> (*) T3 (*) ---> (1) T4
where * indicates many,
I need to create a DAX to add a calculated column in T1, to lookup a field from T4 based on filitered dates from T3, get latest if there are multiple matches.
What would the DAX look like?
Following is what I tried but always gives me blanks:
CalculatedField =
CALCULATE( MAX( T4[FieldName] ), FILTER( T3, T3[StartDate] <= MIN(T1[StartDate]) && (T3[EndDate] > MAX(T1[EndDate]) )
)
Solved! Go to Solution.
Hi Coder_Andy,
I had similar issues in some of my previous projects.
I found that snowflake schemas make for very complicated in Dax (IMO).
Can you convert this to a star schema?
Kerry
Hi Coder_Andy,
I had similar issues in some of my previous projects.
I found that snowflake schemas make for very complicated in Dax (IMO).
Can you convert this to a star schema?
Kerry
I ended up simplifying the data model and converting it to a snowflake schema with 2 levels.
Hi Coder_andy,
I'm glad to hear you got it worked out.
Modeling is important and a good model can save a lot of dax time and frustration.
Kerry
Hi @coder_andy ,
RELATED and RELATEDTABLE should be used here. So you can try to update your formula like this.
CalculatedField =
CALCULATE( MAX( T4[FieldName] ), FILTER(RELATEDTABLE( T3), T3[StartDate] <= MIN(RELATED(T1[StartDate])) && (T3[EndDate] > MAX(RELATED(T1[EndDate])) )
)
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
That didn't work. It throws an error that MIN can only accept field names. I tried changing it to MINX, but did not work.
Since I am adding the calculated field on T1, do I still need to specify RELATED for T1? If I do not write RELATEDTABLE for T3, will the filter include all records from T3, ignoring the relationship?
Unfortunately, I cannot share the data as it is confidential.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |