Direct query - add values from other table to the main table with conditions
Hi, I have created model in "direct query" mode, I dont have experience with it. (I normaly use add Column with combination formulas Calculate + Allexept... now it doesnt work) I need to add values from one column in "table B" to the "table A" for Column xID. I have this conditions: max values from "Column Order" -1 for each Column xID determine value in Column Personal ID in Table A, which is key for connect to Table B. The unique key for this situation in Table A is combination Column xID with Column Personal ID (because values from Personal xID can be repeated). I need to add value from Table B - Column Name to the new Column in Table A (in need can by measure), duplicated for same Column xID values. (Table A has over 23 milions rows)
How Can I Calculate the Years Service of each employee
I have Table EmploymentDetails I will mention the importent columns EmploymentStartDate , PersonnalNumber I want to calculate the Years of Service and the number of months in Years of Service as You shown above I tired to create column to calculate Years of Service " Years of Service = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY(), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY() , MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " this Measure worked correctly it has calculated the Years of Service from EmploymentStartDate of each employee untily today but I want to calculate the Years of Service depend on the slicer the employee's years of service are calculated based on the date I tried to create this Column " YearsOfServices = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " but there is not result could anyone help me to create a column and Measure please ? thank in advance
PYTD / YOY QTD formula issue
I have data that is only giving a quarterly date so the typical PYTD calculation is not working due to the time intelligence. I am trying to use this formula below but am getting an error saying "STARTOFQUARTER" is not of type DATE" What can I do from here? Or can anyone point out an issue with this formula or provide a better functioning formula? YoY PQTD = // Current Quarter To Date VAR CurrentQTD = CALCULATE ( SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added] ), // Filter for the current quarter FILTER ( OWSSummaryByQuarterByEu, OWSSummaryByQuarterByEu[quarter].[Date]>= STARTOFQUARTER('EU Report'[Today]) && OWSSummaryByQuarterByEu[quarter].[Date] <= TODAY() ) ) // Previous Year's Same Quarter To Date VAR PreviousYearQTD = CALCULATE ( SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added]), // Filter for the same quarter of the previous year FILTER ( OWSSummaryByQuarterByEu, OWSSummaryByQuarterByEu[quarter].[Date] >= STARTOFQUARTER ( DATEADD ( 'EU Report'[Today], -1, YEAR ) ) && OWSSummaryByQuarterByEu[quarter].[Date] <= DATEADD ( 'EU Report'[Today], -1, YEAR ) ) ) // Return the YoY difference RETURN ( CurrentQTD - PreviousYearQTD ) / ABS ( PreviousYearQTD )
New column formula that matches the target value of sales person vs family product
Hello all, Please I need your help relating a column that I need to add with a formula that matches the sales target based on family product.Below I have the first table that contain sales data (name) and target based on family product and in the second table I have the same sales data(name) and the family product name connections. In the second table I need to add a formula that reflect the target value (as it it in the first table) of each sales name based on family product. Below the target per family and per sales person. Sales Name Mobility Beyond Connectivity Fixed Connectivity Jane 164,000 381,000 395,000 Ina 40,000 98,800 90,000 Mar 60,000 108,000 135,000 Ves 32,000 87,100 85,000 Irena 32,000 87,100 85,000 I need that in the other table I have Customer Profile to add another column that matches the name and family and input the target value. Customer profile table: Target per sales person should be autopopulated based on the above table info. Sales Person Family Target per sales person. Jane Beyond Connectivity 98,800 Ina Fixed Connectivity 135,000 Mar Mobility 32,000 Ves Mobility 32,000 Thank you!COUNTAX DAX Formel in Power BI Desktop need explanation
Hello all. I have two Lookup tables. One of it has data about learning courses (number of course, Course Start data, the quantitiy of the participants etc). The second one is a person table with learning courses in which they have participated. The both of the tables are exported from Excelsheets. "Cousrse Groups Lookup " Table CS Course number Date of Start Date of End Course activity state Participants Quantitiy Participants successful Quantitiy KPI 1 04.09.2024 25.02.2024 ended 5 5 Not used now 2 27.03.2024 08.05.2024 ended 22 21 Not used now 3 15.05.2024 15.02.2025 active 6 0 Not used now "Person List Lookup " Table Personal Nr Persons CS Course number Participation state Country 1 Lionel 1 1 France 2 Laurence 1 1 France 3 François, Vetea 1 1 France 4 Sashko 1 1 France 5 Vetea 1 1 France 6 Schreiner 2 1 Germany 7 Schneider 2 1 Germany 8 Schindler 2 1 Germany 9 Backenbauer 2 1 Germany 10 Ivaschko 2 1 Germany 11 Lindnder 2 1 Germany 12 Musterman 2 1 Germany 13 Musterman1 2 1 Germany 14 Müller 2 1 Germany 15 Schmidt 2 1 Germany 16 Fischer 2 1 Germany 17 Weber 2 1 Germany 18 Meyer 2 1 Germany 19 Wagner 2 1 Germany 20 Becker 2 1 Germany 21 Schulz 2 1 Germany 22 Bauer 2 1 Germany 23 Schäfer 2 1 Germany 24 Koch 2 1 Germany 25 Schröder 2 1 Germany 26 Neumann 2 1 Germany 27 Zimmermann 2 2 Germany 28 Braun 3 3 Germany 29 Krüger 3 3 Germany 30 Hofmann 3 3 Germany 31 Klein 3 3 Germany 32 Schmitt 3 3 Germany 33 Bley 3 3 Germany 34 Schmitz 0 4 Germany 35 Krause 0 4 Germany 36 Schulze 0 4 Germany 37 Schuster 0 4 Germany 38 Köhler 0 4 Germany 39 König 0 4 Germany 40 Winter 0 4 Germany 41 Schach 0 4 Germany 42 Huber 0 4 Germany 43 Kaiser 0 4 Germany 44 Fuchs 0 4 Germany 45 Peters 0 4 Germany 46 Lang 0 4 Germany 47 Möller 0 4 Germany 48 Weiß 0 4 Germany 49 Jung 0 4 Germany 50 Hahn 0 4 Germany 51 Schubert 0 4 Germany 52 Vogel 0 4 Germany 53 Vogler 0 4 Germany 54 Keller 0 4 Germany 55 Berger 0 4 Germany 56 Winkler 0 4 Germany 57 Roth 0 4 Germany 58 Beck 0 4 Germany 59 Schumacher 0 4 Germany 60 Groß 0 4 Germany 61 Seidel 0 4 Germany 62 Ziegler 0 4 Germany 63 Kuhn 0 4 Germany 64 Bergmann 0 4 Germany 65 Pfeiffer 0 4 Germany 66 Novak 0 4 Czech Republic 67 Noak 0 4 Czech Republic 68 Pietsch 0 4 Czech Republic 69 Kowalski 0 4 Czech Republic (no worry, all names are used for example) In the "Course Groups Lookup" table i have the column "Participants Quantitiy" yet. But it is calculated and prompt manually. Every time if the new course begin, the neu column shall be added into the Table "Course Group Lookup" with recalculation, based on the data in the "Person List Lookup" Table. The both tables have one relationship 1 : x. See the figure below My goal is to add the column "Participants Quantitiy CC" with a measure, which just calculate the participants quantity for every learning course (see the figure). Of course i could use the DAX Formula CALCULATE with FILTER and logical conditions. But in this case i shall add the logic if a new learning course begin and this is not what i want. If you will see attantively on the measure itself, and on the result in the added column, probably you will come to the same conclusion as i - it doesn´t work because i have nearly 30 persons with the course number "0" and due to the logic of the formula COUNTAX i shall have for all of the learning courses 30 persons. But you will be wery surprised because it works (see the measure below and the content of the column in the picture above). EXAMPLE of Quantity = COUNTAX ( 'Person List Lookup', 'Person List Lookup'[CS Course number] = 0 ) There is no matter if i compare with 0 or change the condition to less as 2. The result will be the same - it works as expected. Truly says, i have no idea, but i will be appraciate to you to hear your explanations.
Hep with the formula
Anonymous Anonymous CarlossSainz Hi I have a formula below for calculating the sum of gross risk potential in the table. I want the sum calculation based on the risk name instead of summing up the entire column. I tried the below formula but it is not giving the correct value. I can give an example The total sum of gross risk potential is 100. But the gross risk potential for risk A is 20. I want to get this separate value for all the risks i have in a single column without creating multiple columns. TotalRiskPotentialPerRisk = SUMX( VALUES(Top_Risk_Controls[Riskname]), CALCULATE( SUM(Top_Risk_Controls[Gross_risk_potential]), Top_Risk_Controls[Riskname] = EARLIER(Top_Risk_Controls[Riskname]) ) ) In the above formula, im getting the same values as the gross risk potential column instead of the sum of them based on the risk name. How to get the sum of the values based on the risk name. Please note that the data source is the sharepoint list.DAX formula doesn't sum correctly
Hi all, I have been trying to figure out what I am doing wrong in this DAX formula. I have a normal sales table where I have cost, quantity and sales. Sales = qty x cost but now the following formula calculates the yellow ones and not the greeen ones I would like to sum. What can be seen in the screen shot, the price can change and the correct total sales here would be 229 710€ This is my current formula, any help how to fix this would be great. Thank you. Sales € = SUMX(VALUES('Value Entries'), [Qty x Purch Price]) Br Matt
