Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am a former Tableau user and I trying to figure out how to recrease a cacluated field from Tableau with DAX in PowerBI. The formula I was using in Tableau was:
If {fixed [Academic Year], [Academic Term], [Program], [Degree], [People Code Id], [Stage App Decision], [Create Time]: MAX([DATE + TIME])} = {fixed [Academic Year], [Academic Term], [Program], [Degree], [People Code Id]: MAX([DATE + TIME])} THEN [Stage App Decision] ELSE NULL END
This formula is comparing records based on the max date and context of certain fields. I am thinking utlizing the LASTDATE function seems right, but I am not certain how to set the context. I assume it is using the FILTER function, but I am not sure.
Any thoughts on how to accomplish this in DAX?
Solved! Go to Solution.
Hi @rrhutch,
Based on above sample data, firstly, create new column in Power BI Desktop using the formula below, then change the data type of newfield to Date/Time using Data Type option under Modeling ribbon.
newfield = CONCATENATE(Table[Date],CONCATENATE(" ",Table[Time]))
Secondly, create a new column using the following formula.
Result = IF(Table[newfield]=MAX(Table[newfield]), Table[Stage App Decision],"Null")
Thirdly, create a table visual, see my example.
Thanks,
Lydia Zhang
Hi @rrhutch,
Could you please share raw data of your tables and post expected result here?
Thanks,
Lydia Zhang
Academic Year | Academic Term | Program | Degree | People Code ID | Stage App Decision | Create Time | Date | Time |
2016 | Fall | Grad | Masters | 5555666 | Accepted | 5:56pm | 5/15/2016 | 2:37PM |
2016 | Fall | Grad | Masters | 5555666 | Pending | 5:56pm | 5/15/2016 | 8:59AM |
Above is the dataset after the user filters the data with a date slicer. This is when the measure above is applied.
The result would create the following:
Academic Year | Academic Term | Program | Degree | People Code ID | Stage App Decision | Create Time | Date | Time | Result |
2016 | Fall | Grad | Masters | 5555666 | Accepted | 5:56pm | 5/15/2016 | 2:37PM | Accepted |
2016 | Fall | Grad | Masters | 5555666 | Pending | 5:56pm | 5/15/2016 | 8:59AM | Null |
Basically it is determining the max row of the filtered dataset and giving me a field off of which I can filter the visualization since I only want to include the max row based on a combo of the Date and Time field.
Hi @rrhutch,
Based on above sample data, firstly, create new column in Power BI Desktop using the formula below, then change the data type of newfield to Date/Time using Data Type option under Modeling ribbon.
newfield = CONCATENATE(Table[Date],CONCATENATE(" ",Table[Time]))
Secondly, create a new column using the following formula.
Result = IF(Table[newfield]=MAX(Table[newfield]), Table[Stage App Decision],"Null")
Thirdly, create a table visual, see my example.
Thanks,
Lydia Zhang
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
94 | |
89 | |
32 | |
28 |