DAX
Bonjour les experts dax j'ai importé une table 'sinistres_regles' qui est une jointure de tables en SQL j'ai écrit une mesure pour calculer le nombre de dossier en stock, le résultat match bien avec l'équivalent de ma requête SQL j'ai écrit une mesure pour calculer le coût des sinistres, le résultat matche avec celui de la requête SQL existante par contre quand je calcule le coût des sinistres en stock , le résultat est très différent de celui de la BD. Mes requêtes SQL utilisent les mêmes jointures de tables que ma table 'sinistres regles' dans POWER BI comme base du FROM. je joins à mon message les captures des scripts dxa que j'ai écrit.
Many to many join with filters
Hi All, Trying to work out the problem outlined below in Dax. I have 2 tables, one is customers, the other is page visits. (I'll put examples below) Both tables have customer IDs and multiple rows per customer ID hence the relationship is many to many. I have created a unique table of customers to join both tables to in order to fulfill the one to many requirement in power BI. The point is that I was to see what page visits a customer made before starting the product. The amount of days before is set with a parameter, but for simplicity in this example, let's say it's 1 month. The issue here is that I want to see the visits that customers have made for EVERY product, not for the customer on the whole. Here are the 2 tables, I have added a row ID column in the customer data table, and a "should join with" column in the visit data as an illustration of what i want to happen. Customer Data Row ID ID Product_Start_date Product 1 a 01-jun Car 2 a 15-jun House 3 a 12-jul Pet 4 b 11-jun Car 5 b 27-jun Tree Visit Data ID Visit date Visit type Should join with a 05-apr Car a 28-may Car 1,2 a 12-jun House 2,3 a 01-may Boat 1 a 05-apr House b 10-jun Car 4,5 b 05-may Car b 22-jun Boat 5 b 20-may Pet 4 There is then the third table that carries the unique customer ids, ID a b So customer data and visit data are joined to the unique customer ids table. I would like to be able to choose a customer and see all the visits they made 30 days before buying any of the products, but only 30 days. I tried using min values in my dax filter, but of course thats min on customer level so its much more than 30 days for some products. For example, if I choose customer a in customer data, and filter visits based on the customer's min product_start_date -30 days then for the house product, it will count pages visited between 01-May (1 month before the min product start date for that customer) and 15th jun which is 45 days, not 1 month. Here is the dax function I have with the min, but as i say it doesnt quite do what i want. Customer Key is the unique customers table. CALCULATE( DISTINCTCOUNT('Customer Key'[ID]), filter( 'visits', visits'[VISIT_DATE]>=min('Customer Data'[Product_start_date])-30 ) ) I hope that my examples and explanation make this problem clear enough. Any help would be very gratefully received.
Nested dynamic queries, easy in SQL - difficult in PowerBI/DAX
Assume a single large source table, which must be queried in two steps: Tab1: Key_A Key_B Key_C Val a1 b1 c2 1 a1 b2 c1 5 a2 b3 c1 3 a2 b4 c2 8 a2 b4 c1 1 a2 b1 c2 4 With a variable drillvar = "a2" and two queries you would get: Qry1: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c1' AND Key_A = drillvar GROUP BY Key_B Qry2: SELECT Key_B , Sum(Val) AS SumVal FROM Tab1 WHERE Key_C = 'c2' AND Key_A = drillvar GROUP BY Key_B Key_B SumVal b3 3 b4 1 Key_B SumVal b1 4 b4 8 In the next step, both queries would be joined (the "Nz" function is used here to handle null/blank values): Qry3: SELECT Qry1.Key_B, Nz(SumVal1) - Nz(SumVal2) AS Diff FROM Qry1 LEFT JOIN Qry2 ON Qry1.Key_B=Qry2.Key_B Result: Key_B Diff b3 3 b4 -7 The task seems trivial. I think in PowerBI with DAX plus a Drillthrough value instead of SQL plus a variable it should be easy to do. Unfortunately, I've been racking my brains for two days on how to solve this efficiently, i.e. especially with a large source table. Does anyone here have an idea?Trying to Create new Column/measure thats matched on ID and a Date Range
The three tables I have are shown below Customer Historty Table ID CustomerID PostedDate Old Address New Address 951753 879564213 1/10/2021 123 N Way 789 S Broadway 753951 879564213 1/01/2021 Null 123 N Way 357159 231564879 12/31/2021 Null 456 E Central Customer Table ID (CustomerID) ReportedDate Current Address 879564213 1/10/2021 789 S Broadway 231564879 12/31/2021 456 E Central Order Table OrderID CustomerID SurveyDate 987564213 879564213 1/12/2021 321654987 879564213 1/12/2021 Currently if I wanted to pull order information for OrderID 987564213 it would return multiple address or just the current one. If I only tied it to the Customer Table then I would only get the most current address, not necessarily the one the customer lived at when they made the order. But if I tie it to the Customer Historty Table then I would get multiple address for the order with no way of knowing which one was the ACTUAL address at the time of the order. What I would like is either a new column in the Customer Historty Table that would expand the table to include every single day and put the respective customer address for each day. Or a mesure in the Order Table that could do something similar, by using the order date to determine which Address to use if its after or before they changed address. What I have tried is a mesure that checks the order date and compares it to the latest PostedDate in the customer history table. This did not work as some customers have moved many times a few years so using the latest date wouldnt work.
Join two tables with intersecting dates
Hello, I have - a table with the absences of the agents of my enterprise : start and end dates, number of hours missed - a table with the contracts of the agents : start and end dates, number of work hours per day My goal is to have the number of absence days for each absence by retrieving the right contract line (if there is 2 consecutives contracts lines for one absence, we consider only the first). Example for the tables : Thank you!
Need to implement Join along with Filter in PowerBi
I am creating a report for a Timesheet application where I need to show list/count of employees who has either not submitted their timesheet or even not initiated. I have two tables EmployeeList and TimeEnteries EmployeeList: EmployeeName, EmployeeEmail TimeEnteries EmployeeEmail, WeekOf, Status I want a list like below: Please help me with this.Join whitout direct relationship
hi, i need help with this case: i have two tables linked by link table like this: now I want to add columns from the "AC_CONTACTOS" table to the "AC_GANADOS" table using email as a key, (similar to SQL's left join) but I haven't been able to, because there is no direct relationship between the two tables. Does anyone have any idea how to do it?Adding 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! 🙂
