stand-alone measure giving improper calculations
Hello, I have been scratching my head for days and looking for information that shed light of for the issues I am facing. For further clarification this information is related to a construction project schedule. Issue 1: Improper Calculation I have the following stand alone measures that calculates the following values from a table range of data Start Dat := MIN('DCMA Summary'[Start]) - Finds the minimum date from all the rows from the different activities' start dates Finish Dat := MAX('DCMA Summary'[Finish])- Finds the max date from all the rows from the different activities' finish dates I used a 3rd measure to find the project Duration Project Duration := NETWORKDAYS([Start Dat],[Finish Dat],1,ALLSELECTED(Holidays[Date])) When I try and create a Calculated Column Measure that checks whether individual rows containting Original Duration satisfies the following expression, it doesn't calculate properly (original duration divide by project duration is more that 0.1) Level of Detail = DIVIDE('DCMA Summary'[Original Duration],[Project Duration])>0.1 i.e in some cases if the original duratio is 11 divided by project duration of 97 it correctly calculates as 0.11 = True. But with lower values like an original duration of 3, it incorrectly calculates 3/97 = 1 =True. (Note: I removed the '>0.1' in an attempt to troubleshoot). It is correct for Original duratio of 0/97 = 0 = False Could you please help me fix this? Issue 2: Filtering Data using a Standalone Measure I was able to create several meaures that calculate the different quarters within the project duration. Some were a build up of the measures above. Q1 Start = [Start Dat] Q1 Finish = [Q1 Start]+(([Finish Dat]-[Start Dat])/4) Q1 Duration = NETWORKDAYS([Q1 Start],[Q1 Finish],1,ALLSELECTED(Holidays[Date])) I have data for several projects that was imported into power BI. Each project has a unique Project ID with various activities. Within my report interface I use a filter to show one project at a time. I am trying to tag all the activities regardless of project with a dynamic identifier of which Quarter it belongs to (Q1, Q2, Q3,Q4) but when I use the formula below in the table view, I get the error below Q1 Identifier = IF('DCMA Summary'[Finish]<=[Q1 Finish], "Q1") A circular dependency was detected: DCMA Summary[Level of Detail], DCMA Summary[Q1 Identifier], DCMA Summary[Level of Detail]. How can I dynamically create an expression that is able to tag the correct Quarter to, each activity undertaken at different points for all the different projects in my data base? Issue 3: Stand alone Formula conversion into a table Is there any way to move or create my stand alone meaure as a table, since some filter functions are only able to filter data that is within a table. Or is there a way to express the syntax in a way that makes the DAX formula select the meaure as the entity to filter by? I would like to filter data by the different quarters.
