power bi

243 Topics

Average of categories over vendor
Hello, I'm trying to calculate the category average of vendors, but view the specific vendor. I need to make my formula calculate the score of the category that the selected vendor is in. It currently is showing the same average value for all 4 categories, when they should be different. What do I need to change in my formula? %_VendorScore_Avg_Category = VAR brand = 0.14 VAR eob = 0.13 VAR finance = 0.17 VAR service = 0.18 VAR supplychain = 0.14 VAR technology = 0.09 VAR opportunity = 0.15 VAR SelectedCategory = SELECTEDVALUE ( 'Vendor Scorecard'[Category] ) RETURN CALCULATE ( ([#_BrandScore_Avg] * brand) + ([#_EaseofBusiness_Avg] * eob) + ([#_Finance_Avg] * finance) + ([#_Service_Avg] * service) + ([#_SupplyChain_Avg] * supplychain) + ([#_Technology_Avg] * technology) + ([#_Opportunity_Avg] * opportunity), REMOVEFILTERS ('Vendor Scorecard'[Vendor Name]), 'Vendor Scorecard'[Category] = SelectedCategory )
Solved
torishriver
5 months ago Place DAX Commands and Tips
4.6KViews
0likes
7Comments
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.
Solved
PASCALESAAR
8 months ago Place DAX Commands and Tips
2.7KViews
0likes
10Comments
DAX formula for Monthly Cumulative Backlog Ticket Calculation
Hello, i have found a response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i think the method described of solving the problem is close to the one i m looking for, but in my attempt to download the pbi file and see the calculation, the file is missing. https://www.linkedin.com/safety/go?url=https%3A%2F%2Fcommunity.fabric.microsoft.com%2Ft5%2FDAX-Commands-and-Tips%2FNeed-help-to-create-DAX-formula-for-monthly-backlog-ticket%2Ftd-p%2F1651032&trk=flagship-messaging-web&messageThreadUrn=urn%3Ali%3AmessagingThread%3A2-OTkwNjEwMTYtOTMwNS00MzJkLThmNDQtMjMzNjUwNTFjYTEzXzAxMw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BlGvr8tgdR92f5F9Hpj%2BQRg%3D%3D Futhermore some more information on what i need to achieve and how the solution you provided is close but needs something more to be completed. The Final Output of the report i m trying to implement is the following. The Backlog calculation should provide a cummulative sum of the tickets that remain open (Status = Open) and be added on the following month. For example: On September 2023 Open Tickets (45) - Closed Tickets (23) + Backlog (1) = 23. On your previous response regarding the backlog calculation, i notice that the dax formula doesnt take in consideration the previous backlog tickets if on the previous month none was opened or complete. Year Month Opened_Tickets Closed_Tickets Backlog 2023 June 1 0 1 2023 July 3 3 1 2023 August 0 0 1 2023 September 45 23 23 2023 October 68 51 40 2023 November 72 38 74 2023 December 59 36 97 2024 January 127 69 155 2024 February 135 64 226 2024 March 104 105 225 2024 April 216 120 321 2024 May 138 237 222 2024 June 5 5 222 A few things on my raw data and tables that i need to do the calculation on. Table 1 = Jira Raw that contains the following Columns: Key Status Created Resolved Τckt-1043 Open 3/6/2024 16:41 Τckt-1042 Open 3/6/2024 14:30 5/6/2024 14:30 Τckt-1041 Open 3/6/2024 12:43 My calendar Table is called Calendar [Date] that contains year,quarter,month, week and weekday. My relationships look as follows : Active relationship between Calendar [Date] and Jira raw [created] Inactive relatonship between Calendar [Date] and Jira raw [Resolved] As far as my calculations : Opened_Tickets = if(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))), 0, CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))) Closed_Tickets = IF(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))),0,CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))) Backlog = CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Created] <= max('Calendar'[Date]) || not(ISBLANK(Jira_Raw[Created])))) - CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Resolved] <= max('Calendar'[Date]) && not(ISBLANK(Jira_Raw[Resolved])))) Last but not least i m new to the whole dax and power BI world so any help on achieving the correct calculation of the backlog should be much appreciated. Best Regards.
Solved
Yiannis_N
10 months ago Place DAX Commands and Tips
2.4KViews
0likes
3Comments
Error: A circular dependency was detected
Hi, I'm hoping to get some help on a problem I'm having with a circular dependency. I understand why I'm getting this error, but I'm not sure how to work around it to get the result I require. I have created a sample .pbix file replicating the table and the two attempts shown in this post... but I have no way of attaching it here 😞 What I need to do is compare the start and end date-times of each item in the Type column against the start and end date-times of the Type availability. These all exist in the same column of the query table (generated with power query). I created a calculated table using FILTER on Type = Availability. If I create a visual table and add the relevant columns I get something like the sample data below - the first row is just to make it clear here which table each column is coming from. Query Table Query Table Query Table Query Table Query Table Calculated Table Calculated Table Calculated Table Date Emp ID Type Start Date-Time End Date-Time Type 2 Start Date-Time 2 End Date-Time 2 Revised Start Date-Time Revised End Date-Time 1/9/2025 123 Availability 9am 4pm Availability 9am 4pm 9am 4pm 1/9/2025 123 Activity 1 8am 9am Availability 9am 4pm 9am 9am 1/9/2025 123 Activity 2 10am 2pm Availability 9am 4pm 10am 2pm 1/9/2025 123 Leave 4pm 6pm Availability 9am 4pm 4pm 4pm 1/9/2025 124 Availability 9am 1pm Availability 9am 1pm 9am 1pm 1/9/2025 124 Activity 1 12pm 2pm Availability 9am 1pm 12pm 1pm What I need to do is compare Start Time with Start Time 2 to obtain the last two "revised" columns of the table, for instance like this: Revised Start Date Within Availability Window old 2 = IF ( 'Query Table'[Start Date-Time] < RELATED ( 'Calculated Table'[Start Date-Time] ), RELATED ( 'Calculated Table'[Start Date-Time] ), 'Query Table'[Start Date-Time] ) I also tried something like this after reading the Marco Russo and Alberto Ferrari article on circular dependency with calculated tables (https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/😞 Revised Start Date Within Availability Window = VAR table1 = IF ( HASONEVALUE ( 'Query Table'[Start Date-Time] ), DISTINCT ( 'Query Table'[Start Date-Time] ) ) VAR table2 = IF ( HASONEVALUE ( 'Calculated Table'[Start Date-Time] ), DISTINCT ( 'Calculated Table'[Start Date-Time] ) ) RETURN IF (table1 < table2, 1, 0) The idea is that if start time is < start time 2 (Availability start time) then return availability start time instead, otherwise return start time. But I'd be happy just to return 1 and 0 to see that it works, hence why in the last code example I tried just that. It's the comparision that triggers the error. In each case I get the circular dependency error, obviously because I'm essentially trying to compare the same object (start time) with itself. I know I could do this with power query by duplicating (NOT referencing) the original table, grouping on availability then merging this back to the original table. I want to do this via DAX, not power query, for a few reasons: 1. This is a fact table, and it's quite large as it is with around 2 million rows. 2. There is already a fair amound of data manipulation, so to duplicate the table will slow the refreshes. 3. I will be creating direct query connections to this model for multiple reports, and this operation is only required for one report. Therefore it doesn't make sense to load this duplicate table permanently to the model. It's better to load it to DAX so it's only created at run time when it's required. I'd really appreciate if someone had a solution to how I can compare these columns without the circular dependency error! Thanks Drew.
Solved
drew989898
11 months ago Place DAX Commands and Tips
1.7KViews
0likes
6Comments
Help Managing Dynamic Thresholds (Acceptable error rates) in Power BI
Hi everyone, I'm developing a Power BI report to track manufacturing defects for my organization. We use a defect rate threshold that updates periodically based on trending data. I've attached a sample file that reflects the general structure of our data—it's not an exact match, but it's close. I already have measures in place to calculate both the threshold and the error rate. The challenge I’m facing is how to manage updates to the threshold over time. Specifically, I’m trying to figure out how to: Avoid updating thresholds directly in the source table, since that would affect historical data. Apply logic that allows one set of thresholds for historical data and another for data after a threshold update. I’d really appreciate any guidance on how to accomplish this in Power BI. Note: The file includes a "Proposed Error Threshold" table. This is not the format currently used in the model, but I would prefer a solution that uses it, if possible. Thanks in advance for your help! Sample File
Solved
scoutmedic
1 year ago Place DAX Commands and Tips
1.3KViews
0likes
6Comments
Need help in dax
1. I have a consolidated portfolio table which has fund, security, port wt, esg flag, si flag, asset class, path etc. Now i need to calculate sum(port wt) with filters as security type in (abs,cmbs,mbs) and path in (asset backed). 2. for 4 specific funds i need exclusive filters. for funds ntgf-gbp, ntgflf-eur, ntgf-usd and osgcu sum(port wt) if asset class is sovereign and si flag is 1 that row should be considered and it asset class is sovereign and si flag is 0 that ro should be excluded from sum(port wt). Point 1 filters should apply for all funds including the funds in point2. Point 2 filters should be applied to only those 4 funds. All these needs to be done in single measure and i need port wt.
Solved
sravan872
1 year ago Place DAX Commands and Tips
1.2KViews
0likes
8Comments
Dax measure for if date is smaller than maximum selected date from slicer then sumA else SumB
I want to show a matrix table showing actuals if dates is smaller than e.g. 31 May 2025 then budget values if after this date I was using this formula FORECAST = IF(FIRSTDATE(Budget_data[Date]) <= DATE(2025,05,31),sum('PnL Data_2025'[Amount]),sum(Budget_data[Amount])) but I now want the dashboard user to select a dates on the slicer instead of me hard coding the cut off date.
Solved
ILLANA_N
1 year ago Place DAX Commands and Tips
839Views
0likes
5Comments
DAX equivalent for sumif
Hi In Excel I'd use a sumif function to add 2 columns and if they added to 2 to return a 1 otherwise a 0. I've tried every which way to do this using DAX but it's always the wrong answer compared to excel - how do i do? example In excel to create the column called call 1 and call 2 i'd use this formula: =IF(SUM(FL2:FM2)=2,1,0), how can i replicate this in DAX call 1 call 2 call 1 and call 2 1 1 1 1 1 1 1 0 0 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 0 0 1 0 0 1 0 0 1 1 1
Solved
ottster
1 year ago Place DAX Commands and Tips
1.2KViews
0likes
6Comments
Need a Dax to make a measure for counting the rows on a dynamic table using 2 parameters [Power BI]
The Row count needs to be displayed on a Key card next to the table visual. The Data of the Table is from two Parameters (Multi-selection) : 'Selected Dimensions P3'[Select Dimensions] 'Selected Measures P3'[Select Measures] Rows on the Table keeps changing according to the field selection by the viewer. The row count on the key card should simultaneously change to the current number of rows displayed on Table Visual.
Solved
Hitha
1 year ago Place DAX Commands and Tips
754Views
0likes
4Comments
Created a DAX Query Tutorial Video on Youtube
Just dropped a new video on writing DAX queries in Power BI! https://youtu.be/T_-Z9Mstygk?si=IC1QrA6E3iGWdCJH If you're diving into data analysis or trying to level up your dashboard game, understanding DAX is a must. In this video, I break down: What DAX is and why it’s powerful 🔹 The difference between calculated columns and measures 🔹 How to write clean, efficient queries 🔹 Real-world use cases that make your dashboards smarter Whether you're just starting with Power BI or looking to optimize your reports, this guide covers it all with simple explanations and practical examples.
Solved
HusainCementwal
1 year ago Place DAX Commands and Tips
3KViews
1like
4Comments