DAX Calculation find max value until certain date per employee (many-to-many)
Hello guys, i have a problem that is driving me nuts and I am coming to you for help. I have two tables: The first one details the working schedules per employee. It shows the schedule and the date it was submitted. The second one is an order table and it contains working orders, a date and the employee. I would like to get two new columns in the second table specifying the employee schedule. I specify columns as I need to do further calculations and I need to visualize the date without the employee field displayed. Even though any idea is welcome. Notice that both tables can have multiple times the same employee and the dates are not comparable! Example: Any recommendations? Thank you so much
How to multiply percentage from dimension table with amount from fact table - many to many relation
Hi, I have a problem getting the appropriate data from my DAX formula. Here's my model: fact dimBank dimSecurites Fact table is a day to day table with debt amortization plan ordered by tranche (%dimSecurities) dimSecurities is a table with all specific data for the vessel, each having unique key %dimSecurities dimBank is a table where the split between multiple banks, where a %dimSeucrities sometimes occur 3-4 times depending on how many banks involved This is a simplified version of my data model: Looking at this table, the data is correct when I add other dimensions, like "Share" or even better "%dimSecurities", but the total sum is incorrect as it is a result of the average share multiplied by total debt... However, I would like to show these data graphical in a barchart, but then I cannot add the aggregation, hence the total sum is being used, wich is incorrect, 12,017,545 when the correct sum should be more like 7,961..... etc This is my DAX formula... debt by lenders = CALCULATE(SUMX('factSecurity',factSecurity[Loan Balance Start] * LOOKUPVALUE(dimT16_Valutakurs[Currency Rate], dimT16_Valutakurs[Dates], Max('Calendar'[Date]), dimT16_Valutakurs[FRA_VAL_KODE], factSecurity[Currency])), FILTER(factSecurity, factSecurity[Dates] = Max('Calendar'[Date])))* AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share]) Any help is much appreciated 🙂 Thanks E
select a single value from a many to many relation base on a field in the fact table
hi everyone! so i need to create a field for table in a report, this field comes from a table name station, my problem is that the data comes from a many to many relation and depending on a value from my fact table could be one or another station, along other conditions. to give you an idea here is the model, its one fact table (Data_Workitems), two dimension tables (station & Pstn number) and a bridge table (stationpstnnumber) meaning one pstn number could be assign to many stations and a station could be assign to many pstn numbers 'stationpstnnumber'[stationid] * -<-1 'station'[id] 'stationpstnnumber'[pstnnumberid] * <--> 1 'pstnnumber'[id] 'DATA_Workitems'[toAddress] * -<- 1 'pstnnumber'[name] in the Data_Workitems table the pstn number is know as the field name ToAddress, its the phone number to which clients dial to, so from there we have the ToAddress which could be assign to many stations, now to get the right station of the many that could be asign to the pstn number there is also another field on the fact table Data_Workitems called fromAddress which is the phone number from where the client is calling, the first three digits of this number is called the area code, now the station table has also a field called areaCode that contain muliple 3 digit numbers separated by a space like this: so from the many stations assign to the pstnnumber (or the ToAddress on the fact table) we are going to return the one that contains the 3 digits from the FromAddress field of the fact table, so depending on the fromAddress the station name could vary, if non of the 'station'[areacode] contains the first 3 digits of the FromAddress a blank() is return, if more than one 'station'[areacode] assin to the ToAddress field contains the first three digits of the FromAddress return the one with the highest 'station'[modifiedAT] if you know the solution to my problem or you reccomen some tips would be much appreciated, i have been struggling with this for a while thanks
Problem many to many building dates
Hi Everyone, I appreciate with you guys can help me with this DAX, I need to build a date (Ex : 20220213), using 2 tables. At column dax_YearMonthNo I need this date. Using this DAX Formula i could get YYYYMM (Image1) when i try to make a lookup to bring the earliest date from Calendar it fails, (already tried using Earlier) Image 1 Image 2 Calendar Table 1
