Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LMSReportsHelp
Frequent Visitor

Creating a column based on Date in the past

I have 2 tables, I am trying to create a new column based on Table1.ColumnA (text) and Table2.ColumnB (date).  The tables are set with a  One:Many relationship (the person is assigned 1 class, but might register for multiple class times).

 

It's pretty simple in a lot of ways, but I must be doing something wrong because I keep checking the 1 person I know (right now) who meets the criteria and they aren't getting my text in their column.  The part in bold (the date) seems to be where I'm hitting the snag.  I've tried several different functions and MAX is the only one I'm not getting an error when I complete my formula, but I'm pretty sure it isn't doing what I want. 

 

Missed Class =
    var PastClass = IF(TODAY()>MAX(Table2[ColumnB]),1,0)
    var CompletStat = IF(RELATED('Table1'[ColumnA])="Incomplete",1,0)
    RETURN
    CALCULATE(IF(PastClass+CompletStat=2,"Missed Class",""))

Is there a better way to write "if the class is the past and the student shows incomplete, they missed class"? I suspect what I need is something simple and I'm just being a dummy.
1 ACCEPTED SOLUTION
LMSReportsHelp
Frequent Visitor

I apparently was over-complicating my formula.....

Missed Class  =
var PastClass = IF(TODAY()>Table2[ColumnB].[Date],1,0)
var CompStat = IF(RELATED(Table1[ColumnA])="Incomplete",1,0)
RETURN
CALCULATE(IF(PastClass+CompStat=2,"Missed Class",""))

View solution in original post

4 REPLIES 4
LMSReportsHelp
Frequent Visitor

I apparently was over-complicating my formula.....

Missed Class  =
var PastClass = IF(TODAY()>Table2[ColumnB].[Date],1,0)
var CompStat = IF(RELATED(Table1[ColumnA])="Incomplete",1,0)
RETURN
CALCULATE(IF(PastClass+CompStat=2,"Missed Class",""))
ray_aramburo
Super User
Super User

I would tackle this with Power Query but would need a sample of your data removing any sensitive information to provide you a solution example.





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Not to sound dumber than I already do, but how would Power Query help me create this column? I've already done some transformation of my data, but that still means I'd need to get the DAX formula right, wouldn't it?

The functionality of ifs, merging an comparing past dates can be done faster and in my opinion way easier with Power Query than with DAX. At least for this scenario. However, I could still provide you a DAX solution but would need to take a peek to a sample data. 





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors