Combining three CONCATENATEX Measures with results from two Tables
Hello, I am trying to create a status report that pulls in text updates from two tables (two sharepoint lists). Each Table is realted in a Many to 1 with an Intake Table that contains my projects. Working seperatley I've created three Dax Measures that uses CONCATENATEX to return a bulleted list of updates that meet certain criteria. Because of this, each Measure has a Filter with conditions to the table I am pulling data from. Each Measures works alone, however, when I attempt to combines these measures I find that I am only able to apply context to one table. For example, if I use an ALL statment in a nested calcuation this will clear results from the first measure and if I do not clear results the second measure will not return results. To me, this means the CONCATENATEX is running into a null or blank value which means the one of the Filters is not providing a needed context. For refrence this is my combined measure that will only return results for the second part: Measures use three tables. PMO Project Intake has a one to many realtionship with PMO Status Updates and PMO RAID LOG. PMO RAID Log and PMO Status Updates are not related (would need to be many to many and not sure it would help?) Next these are the two measures that work correctly when added seperatly: CARBULLTEDRAID = "Issues & Decision Points:"& UNICHAR(10) &CALCULATE(CONCATENATEX( FILTER('PMO RAID Log', 'PMO RAID Log'[Risk Type]<>"Risk" && 'PMO RAID Log'[Status]= "Open"&& 'PMO RAID Log'[FieldValuesAsText.ProjectName]=SELECTEDVALUE('PMO Project Intake'[Project Name])), UNICHAR(8226) & " " &('PMO RAID Log'[Description])&"-"&('PMO RAID Log'[Resolution])& UNICHAR(10)& UNICHAR(10) ,) ) CARBULLTEDLIST = "STATUS:"& UNICHAR(10) &CALCULATE(CONCATENATEX( FILTER('PMO Status Updates', 'PMO Status Updates'[CommentType]<>"Next Steps" && 'PMO Status Updates'[IncludeinCurrentReporting?]= True&& 'PMO Status Updates'[FieldValuesAsText.ProjectName]=SELECTEDVALUE('PMO Project Intake'[Project Name])), UNICHAR(8226) & " " &('PMO Status Updates'[Created])&"-"&('PMO Status Updates'[CommentType])&"-"& 'PMO Status Updates'[FieldValuesAsText.StatusText], UNICHAR(10)& UNICHAR(10) ,'PMO Status Updates'[Created],DESC) ) This is the combined value which is not working: Combined Measure = VAR Part1 = [CARBULLTEDRAID] VAR Part2 = CALCULATE( [CARBULLTEDLIST], ALL('PMO Status Updates') ) RETURN Part2 & " | " & Part1 The above only returns the results for measure [CARBUILTED RAID] and not the others: I underestand I could create a new table with all of these statues together in M, however, I am wondering if I am missing something the CONCATENATEX Statment. Is is possible to combine the results of multiple measures that use CONCATENATEX and keep the context for each measure? And if so how can I nest the DAX that restest context without having the rest apply where it isn't allowed? Thank you!
DAX logic needed to determine order lines value of new, existing or shipped orders
Hello! I am trying to come up with the DAX logic to determine order lines value of new, existing or shipped orders. New order value needs to show a sum of USD of new order lines in the most recent week (orders not present in previous week). Existing order value needs to show a sum of USD of order lines that exist in both current and previous weeks (summing only values in current week to avoid duplication). Shipped value is sum of orders that exist in previous week, but not current week. I can’t split table into weeks, no new tables in DAX based on my fact table. I must only operate in DAX, no MS Query solutions. I am not restricted on the number of columns or measures I can create with DAX. I have ‘Calendar’ table in addition to my fact ‘Orders’ table. Here is sample of fact ‘Orders’ table:
