CROSSJOIN not giving required results - too many duplicates
Hello, I have created a new table via a CROSSJOIN between a Agreement Additions table and a Dim_Calendar table. See below: I have connected them using the StartofMonth column from Agreement Additions and Date column from Dim_Calendar (relationship model). Below is the DAX for the new table 'Contracted': Contracted = CROSSJOIN ( 'AgreementAdditions & List', SUMMARIZE ( FILTER ( 'Dim_Calendar', 'Dim_Calendar'[Date] > MIN ( 'AgreementAdditions & List'[START_DATE] ) && 'Dim_Calendar'[Date] < MAX ( 'AgreementAdditions & List'[END_DATE] ) ), Dim_Calendar[StartofMonth] ) ) Below are some example columns from the Agreement Additions table where the key focus is: As you can see the above table has a start and end date per agreement and a monthly billing amount. My goal is to repeat the number of rows per month between the start and end date. However, the resulting 'Contracted' table is not correct. See below why this is: The above table is what is formed from the CROSSJOIN (between Agreement Additons and Dim_Calendar). The above example agreement has a start date from Aug 2022 - Aug 2023, therefore I would expect 12 rows (1 row per month) of £9.40 for this example, but instead I get 56 rows which is strange. When I plot a matrix viz, this is what I get: What i'm expecting to see is the below results: What am I doing wrong?
Dividing two values in different tables
I currently have 2 tables: Sheet 1 and Census data. I have built the census data to show: Census Data Age/Gender = DATATABLE( "Age Group Name",STRING, "Male",INTEGER, "Female",INTEGER, "Total",INTEGER, { {"0-4 yrs",169731,161784,331515}, {"5-14 yrs",344848,330189,675037}, {"15-24 yrs",292492,283960,576452}, {"25-34 yrs",318401,341009,659410}, {"35-44 yrs",368527,378354,746881}, {"45-54 yrs",311036,315009,626045}, {"55-64 yrs",252556,256402,508958}, {"65-74 yrs",184462,189046,373508}, {"75-84 yrs",89313,107191,196504}, {"85+ yrs",23062,44493,67555} } ) What I'm trying to do is take the count per age group in Sheet1 and multiply it by the Total in the census table e.g. first value of 0-4 yrs is 5 therefore (5/331515)*100000 = 1.5 Thanks in advance https://www.dropbox.com/s/c3hfci8jabydxv1/Pareto.pbix?dl=0Crossjoin measure with filter and subtracton
I have a measure that counts number of working days in the month[Measure 5]. What I want to do is subtract the [Rules] values from this e.g. 23 working days - 2.5 days according to the IF statement in rules calculation My DAX is: Measure 7 = CALCULATE ( CALCULATE( countx( SUMMARIZE( CROSSJOIN('Employee Absence Full Table','DimDate','Query1'), 'Employee Absence Full Table'[PERSON_ID],'DimDate'[Date],'Query1'[Rules]),'DimDate'[Date]), FILTER(DimDate,DimDate[WorkingDays] <>BLANK()) ) -COUNTX('Query1','Query1'[Rules]) ) However when I run my Measure 7 DAX I get (23-1) not 2.5: Is there a way to select the individual values in [Rules] and subtract them from a measure? ThanksCreate a table/matrix with related data of 2 queries
Hi, I'm having this problem: I have this 2 tables and has a relationship 1 to many by OP, I would like to create a column that can lists every "new op" that have the same code of the "old op", I'm thinking in something like this: or something like this it's the same idea but different format, eather of these two should do the work, but so far i can not reach a column like that, any help would be very much appreciate. Thanks.Values return in blank becuase different date
Hello there! I urgently need your help with this. I need in anyway place the values of the source OE parallel with the source PR because is giving me the values in blank. It is the same table, then what I can do please? Your support is truly appreciated. I was w Month Actual Date Source Hours Estimated Hours Cost 2/1/19 2/5/19 PR 8 2/1/19 2/6/19 PR 8 7/1/18 7/1/18 OE 200 5000Adding a new column based on ID with relationship between tables
Hi! I have two tables with a relationship based on order id. What I'm trying to achieve is to add "date" column from "Order Product Histories" to "ORDER PRODUCTS" table. The "date" in "Order Product Histories" table indicates the date of booking and the "date in ORDER PRODUCTS" table indicates the execution date. The case is I have multiple DAX measures already created and I cannot use both relationships between my calendar table and "ORDER PRODUCTS" with "Order Products Histories" at one time. I would like to use the date from "Order Product Histories" table to filter my measures, so I thought it would be the best to add a new column to "ORDER PRODUCTS" table. Looking forward to any tips to make it efficiently work! 🙂Changing a variables and stored values based on looping conditions that compares 2 objects?
Is it possible to create a loop that checks a condition and then applies new values to the original variables? More specifically I have cross-joined a table to compare elements against each other and based on that first comparison they are then compared again and their values are reduced until one of the two comparisons hits zero which will then do an If statement to label the “winning” element of the two based on the percentage of its starting value that remains for the “winner”. Here is an example of the data: Elements to Compare Numeric Value Change Starting Value Obj Change Value AW .75 6 CR 4.44 40 I’d need the above info do the following Compare each element against each other to be placed in a matrix with your element of choice on the first column and the opposing element as the headers of each other column. Each cell between them will initially look at the “starting value” minus the “numeric value change” and return the result. E.g. AV with a starting value of 6 is compared to CR with a Numeric Value Change of 4.44 the compared value for “round 1” is 6 - 4.44 displaying 1.56 as the remainder going into “round 2”. CR would also have its starting value of 40 and subtract the Numeric value Change from AW (.75) leaving the “Round 2” starting value for CR as 40 - .75 at 39.25 starting value for CR. It will then take that results from “Round 1” and then alter the starting values to subtract the numeric value change again. Same elements would now be AW vs CR but AW = 1.56 Starting value instead of 6 and CR’s new starting value would be 39.25 It will run through the calculations again 5 times or until one of the two elements hit’s 0 first. AW Starting Value vs CR Numeric Value Change = 1.56 – 4.44 resulting in AW new starting value for round 2 stored as -2.88. CR Starting Value vs AR Numeric Value Change = 39.25 - .75 resulting in CR new starting value of 38.5 Since Round 2 ends with AW “starting value” as less than 0 the loop would end and then declare CR the standing element at (New Starting Value of 38.5/Original Starting Value of 40)*100 or at 96.25% remaining “Starting Value. As a measure it could then run this comparison in the matrix after cross-joining the duplicated tables unless someone has a better alternative for that as well? Big goal here is knowing if I can loop the formula and update the variables for “Starting” values while pulling in the variable for “Numeric Value Change” which may or may not have that value changed as the “starting value decreases over time or “Rounds” Can we update variables with new values and step through or loop to continue calculating the result until a final value of 0 for one of the two compared elements is achieved or 5 rounds of comparisons have passed?
