Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
and if not use another value. Trying to wrap my head around the DAX for this. I have a related table of work orders and if that work order is found then I need to grab a date from that table. If it is NOT found, then I need to just use the current date in the main table.
What I have right now is something like this:
LastStartDate = VAR WOExists = CALCULATE(COUNTROWS(SecondTable,FILTER(SecondTable, SecondTable[WorkOrder] = EARLIER(MainTable[WorkOrder]))>1
RETURN If(WOExists,SecondTable[WorkOrder],MainTable[WorkOrder])
And I am doing this as a column, not a measure
Thanks for the information
Solved! Go to Solution.
@guyinazo The direction of the relationship is really important here - what does your data model view look like please?
From your question it seems like you're wanting to get the last date from the Fact (WO transactions??) table and pull it into the Dimension table that has a list of each WO only once??
If so, you can try:
Last WO Date =
MAXX( FILTER ( FactTable, DimensionTable[WOid] = FactTable[WOid]), FactTable[Date] )
Then you can nest that in an IF statement so if it's blank, return current date (not sure what you mean by current date in main table - again please provide more info on your relationships).
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@guyinazo If you are creating a calculated column in MainTable, this should work out , works with or without relationship
LastStartDate =
CALCULATE (
CALCULATE (
MAXX ( SecondTable, SecondTable[Date] ),
TREATAS ( VALUES ( MainTable[WorkOrder] ), SecondTable[WorkOrder] )
)
)
@guyinazo If you are creating a calculated column in MainTable, this should work out , works with or without relationship
LastStartDate =
CALCULATE (
CALCULATE (
MAXX ( SecondTable, SecondTable[Date] ),
TREATAS ( VALUES ( MainTable[WorkOrder] ), SecondTable[WorkOrder] )
)
)
Seems to work, but I am getting a warning "Expressions that yield variant data-type cannot be used to define calculated columns"
@guyinazo The direction of the relationship is really important here - what does your data model view look like please?
From your question it seems like you're wanting to get the last date from the Fact (WO transactions??) table and pull it into the Dimension table that has a list of each WO only once??
If so, you can try:
Last WO Date =
MAXX( FILTER ( FactTable, DimensionTable[WOid] = FactTable[WOid]), FactTable[Date] )
Then you can nest that in an IF statement so if it's blank, return current date (not sure what you mean by current date in main table - again please provide more info on your relationships).
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
But now that I am re-reading your reply, let me try that
Not really. So I have a main table with work orders and other fields. This looks up two other tables that are related by Work Order. This main table has a created column called LastStartDate. If the work order exists IN the first table, use that LastStartDate, else use the LastStartDate from the second table.
User | Count |
---|---|
85 | |
72 | |
71 | |
67 | |
56 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |