How to count the row count matching the selected data set, where the issue type match
Hi, I have built a star schema as below. I have two flat files loaded as test_execution_summary and defect_report. Records in the test execution summary is filtered using the practice name and then again the project name. After that I am using a visual filter to filter in cycles related to the recent release, which I calculate through a measure. Relationships between tables are as follows: - defect_report[IssueKey] = test_execution_summary[ExecutionDefect] - test_cycle_details[cycleID] = test_Execution_summary[CycleID]] In the defect table there are two types of issues, bugs and defects. I need to calculate the bug count of each cycle, selected for the latest release. I tried to use below measure, but it is making the bug count value empty. Bugs Count 1 = CALCULATE(COUNTROWS(FILTER(defect_report, defect_report[Issue Type]="Bug")), RELATEDTABLE(test_execution_summary)) I have been trying different ways to get this to work, but i can't figure out the way. I am totally new to Power BI. Appreciate if someone can help me to figure out what i am doing wrong and how to fix this.
Calculate Date (Gantt Chart)
Hello, i'm making a Gantt chart where i have several projects and these projects have several tasks but I would like only the start and end date to be displayed for 1 project, for example in this project i have 4 tasks So when they are added to the diagram they apper like this And i just want a line on the Gant chart with the start on August 8 and the end on Agust 17 and not 4 lines. What can i do?Dynamic Column Headers depending on a Slicer selection
Hi All, Hi PBICommunity , PBCommunity , I have a question , is there any way to make the column names in a Matrix/Table dynamic and depending on a slicer selection ? I tried to solve this with field parameters but the name argument in the field parameter formula can't read the slicer selection ( The SELECTVALUE function does not allow to change the name , it returns blank . Could you please suggest a solution within field parameters or any other method. Thank You in advance , Chaimae
Function Calendar Wrong WeekNum
Hi there, I have a date table with the following DAX-Code: Dates = CALENDAR(DATE(2018,01,01),DATE(2030,12,31)) According to the documentation, the WeekNum function works like this: "By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year. However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition." For the year 2021 happens exactly this case. There are 1.1. - 3.1. in the week 53 of the year 2020. But the the WEEKNUM-Functions shows this three days as the week no. 1 of the year 2021. The result, the WeekNum numbers don't match with the no. in europe. How can I fix mismatch? Thanks for help Cheers Mike_CH
Division in Matrix
Hi, Please help me to build DAX to meet below requirement, I have created 2 MATRIX Visual Revenue and Expense Account_ID starting with 4 is Revenue Account Account_ID starting with 5 is Expense Account So I have made Visual Level Filter for Revenue Account_Type='Revenue' & Expense Account_Type='Expense' I written below DAX for JAN-2022 A_JAN = CALCULATE (SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1) Now I have requirement to get Percentage of Expense where Different Account Expenses should be divided with Total Revenue 166,801021 Example : 62,794,480 / 166,801,021 46,539,963 / 166,801,021 Below DAX for Revenue is working fine. A_JAN% = DIVIDE( [A_JAN], CALCULATE( [A_JAN] ,ALLSELECTED()) ) But below DAX for Expense is not working correctly. E_A_JAN1% = DIVIDE( CALCULATE ( SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1,PBI_MIS_TB[TB_ACCOUNT_TYPE]="REVENUE" ), CALCULATE( [A_JAN] ,ALLSELECTED()) ) Thanks & Regards JamsherDAX: Moving Range Average & Standard Deviation
Hello. I am trying to build I-MR charts on the month level and am having difficultly getting the average & standard deviation of my "Moving Range" measure. Here is a sample of the excel data I start with. The "Date" column is a calculated column created in Power BI after the data is loaded: Month Year metric_a metric_b Date 1 2020 5.5 1/1/2020 12:00:00 AM 1 2020 4.5 1/1/2020 12:00:00 AM 1 2020 6.2 1/1/2020 12:00:00 AM 2 2020 3.3 2/1/2020 12:00:00 AM 2 2020 6.7 2/1/2020 12:00:00 AM 2 2020 5.4 2/1/2020 12:00:00 AM 3 2020 2.2 3/1/2020 12:00:00 AM 3 2020 10.8 3/1/2020 12:00:00 AM 3 2020 7.8 3/1/2020 12:00:00 AM The measure I am using for the I-MR charts is a ratio between the two metrics. The DAX formulas I used to create the ratio measures are: Sum_A = SUM(Sheet1[metric_a]) Sum_B = SUM(Sheet1[metric_b]) Ratio = DIVIDE([Sum_A], [Sum_B]) I used the PREVIOUSMONTH() function to calculate the ratio value from the previous month and then subtract the two measures to get Moving Range: Ratio Last Month = CALCULATE('Measures Table'[Ratio], PREVIOUSMONTH('Date Table'[Date])) Moving Range = ABS([Ratio] - [Ratio Last Month]) Viewing a table of the results everything looks accurate: Year Month Ratio Ratio Last Month Moving Range 2020 January 62.00% 62.00% 2020 February 54.00% 62.00% 8.00% 2020 March 60.00% 54.00% 6.00% 2020 April 51.90% 60.00% 8.10% To build the LCL and UCL lines for the I-MR charts I need the average and standard deviation of both "Ratio" and "Moving Range". I attempted to calculate these metrics with these measures: Ratio Avg = AverageX(ALLSELECTED('Date Table'[Date]),[Ratio]) Ratio StdDev = STDEVX.P('Date Table',CALCULATE([Ratio])) Moving Range Avg = AverageX(ALLSELECTED('Date Table'[Date]),[Moving Range]) Moving Range StdDev = STDEVX.P('Date Table',CALCULATE([Moving Range])) I placed the average and standard deviation measures in a card. The average and standard deviation are correct for the Ratio measure but incorrect for the Moving Range measure: According to Excel the average Moving Range is 8.88% with a standard deviation of 11.62%. Any idea what I need to change to get the correct results for Moving Range? Any help is greatly appreciated, thank you! Link to .pbix: https://drive.google.com/file/d/1CWtFiHCjNhANGD9XCwvyIo8dvaeerry-/view?usp=sharingNeed help with DAX measure to show column with Blank value in Table
Hello all, I would need your help for the below requirement. I have tried myself but not meeting complete requirement I have table with below column which shows open/close response details. In case of closecategory within 24hrs and after 24Hrs , "closereason" gets translated to defined conutry languages and "emailcloseby" will be tagged to en always and doesn't store value for New/Late closecategory responseid closedresponseid closecategory emailclosedby closeReason languagecode country a1 b1 within 24Hrs [email protected] testing en Belgium a1 b1 within 24Hrs testen nl Belgium a1 b1 within 24Hrs essai fr Belgium a2 b2 after 24Hrs [email protected] test en Portugal a2 b2 after 24Hrs teste pt Portugal a3 Late Italy a4 Late Poland a5 New Poland a6 New France Report should have slicer of closecategory and languagecode(taken from dimension table) along with table visual which will have all details. Languagecode slicer should show data in table based on selected value and If nothing is selected show value any among them in case of more than 1 language for one country. I have written below measure which I should use instead of column closereason in table which filter data correctly based on language but requirement is to show record(with blank value for column closereason)in table when closedcategory is selected all which also includes closecategory=New/Late(langugage code is blank).When closecategory is New/Late then closereason and languagecode will not have data. Translated_Closereason = SWITCH(SELECTEDVALUE(Sheet1[languagecode]), "fr",MAXX(FILTER(Sheet1,Sheet1[languagecode]="fr"),[closeReason]), "nl",MAXX(FILTER(Sheet1,Sheet1[languagecode]="nl"),[closeReason]), "pt",MAXX(FILTER(Sheet1,Sheet1[languagecode]="pt"),[closeReason]), "pt",MAXX(FILTER(Sheet1,Sheet1[languagecode]="pt"),[closeReason]), MAX(Sheet1[closeReason])) Similarly, I have to write one more DAX for Email which should show always show the value irrespective of languagecode. Tranlsate_emailcloseby= MAX(emailcloseby) which again having same problem like closereason as row itself gets disappeared Please see screenshot and refer the bottom table which doesn't show data for New/Late closecategory. If there is nothing selected from both slicers then result should be like RESULTS : responseid closedresponseid closecategory emailclosedby closeReason country a1 b1 within 24Hrs [email protected] testen Belgium a2 b2 after 24Hrs [email protected] teste Portugal a3 Late Italy a4 Late Poland a5 New Poland a6 New France Please help with this. Thanks very much in advance !Override date filter in Power BI with months before the filtrate
Hi, I'm trying to make sure that when I filter the month, the table returns the 5 months before that filtered month. I'm new to dax language and I don't have so much advanced knowledge and I found a way to solve this link: https://towardsdatascience.com/override-date-filter-in-power-bi-743b9e8b9b2 What I did was create this measure Value of docs -5 month = VAR MaxDate = MAX (Date [Date]) VAR MinDate = DATE (YEAR (MaxDate), MONTH (MaxDate) -5, DAY (MaxDate)) VAR Result = CALCULATE ( DISTINCTCOUNT (Table [DOC_ID]), FILTER (table, table [Date]> = MinDate && table [Date] <= MaxDate) ) RETURN Result the table [data] is the main date of my data table and the date [date] is a table just like the date, as if it were a fatto table You are returning the last 5 months regarding the maximum date not what was filtered, how would you do this measure to solve this?
