dax power bi

31 Topics

DAX Measure Returning Incorrect Results
Hello, I’m facing an issue with a DAX formula in Power BI. The results are not matching the expected output, even though the relationships and filters seem correct. Any suggestions would be appreciated. Thank you!
Solved
Remah_Ramadan
8 months ago Place DAX Commands and Tips
929Views
1like
5Comments
Show Task on Table Based on Selected Week
Hi, I am working on building table that show all tasks with Status "Not Start", "On Hold", "In Progress" and closed task (by Closed Date) based on selected week. Basically I have a project table, with structure like below: Task Created Date Start Date Closed Date Target Date Status A MM/DD/YYYY - - - Not Started B MM/DD/YYYY - - - On Hold C MM/DD/YYYY MM/DD/YYYY - MM/DD/YYYY In Progress D MM/DD/YYYY MM/DD/YYYY MM/DD/YYYY MM/DD/YYYY Closed And I created a Calendar table, with column Date (extracted from Min(Created Date) and Max(Target Date)) and column YearWeek (WW'YY). And I connect it (column Date) to project table (column Created Date). The issue that I encounter is that, the visual table will show almost every task because of this relationship. So I try to use kinda 'cheatsheet' way by creating a calculated column at the project table: ReferenceDate = VAR TaskStatus = Project[Status] RETURN SWITCH ( TRUE(), TaskStatus IN { "Not Started", "In Progress", "On Hold" }, Today(), TaskStatus = "Closed", WorkItems[Closed Date], BLANK() ) When I select current week, yup, the task that match the condition did appear. Yet, when other week is selected, it will be blank. Is there anyone ever encounter this before or have experience with it when develop gantt chart? How you overcome it? Any guidance will be helpful. Thanks in advance.
Solved
KJChin
1 year ago Place DAX Commands and Tips
434Views
0likes
1Comment
Problemns with IF logic VAR
Hello everyone, who are you guys doing? I am preparing a comparative dashboard about two dev platforms and encountered a logical problem. Basically, I have to apply a conditional logic within DAX which I thought would be easy. Every time I filter a value (visual filter) on the page, it will perform a calculation by taking fixed values and multiplying them by the value selected in the filter. So, hypothetically speaking, if my Project A does not have available data, I have to perform this calculation for it: (Fixed Jenkins Median / Fixed Azure Median) * Filtered project median value. The measure was implemented as follows: if the calculation needs total values from both platforms, and when I filter by project, I created a VAR for the total of Azure and Jenkins but FIXED, so I can derive the values by division. VAR for acronyms and VAR totals were also created to perform the other calculations. Has anyone done something similar and can help me? Here is the DAX with logic: // CONDITIONAL VAR JenkinsResult = IF( ISFILTERED(dGeneral[Acronym 2]), IF( ISBLANK(FilteredJenkinsMedian), (FixedJenkinsTotalMedian / FixedAzureTotalMedian) * AzureAcronymMedian ), TotalJenkinsMedian ) VAR Platform = SELECTEDVALUE(dGeneral[Platform]) // SWITCH VAR FinalResult = SWITCH( Platform, "Azure", TotalAzureMedian, "Jenkins", JenkinsResult ) RETURN FinalResult
beldev
2 years ago Place DAX Commands and Tips
826Views
0likes
2Comments
DAX for Week Over Week comparison between two years
Hi Everyone. I have a task where I need to calculate week over week lease renewal comparions between two years and broken down by Regions. In my Renewal Report table I have a column named "Signed Date" where I can see that if there's a date in that row it means that there was a renewal signed. The below DAX calculcates for year over year but not week over week. I created a Calendar table that contains "Date" "Month""WeekNo" and "Year". The Calendar table has a One to Many relationship with the Renewal Report table. can someone pleaseee help! Thank you Renewals_Current_and_Previous_FY_Percentage = VAR Renewals_Current_FY = CALCULATE( COUNTROWS('Renewal Report'), FILTER( 'Renewal Report', 'Renewal Report'[FY] = 2024 && 'Renewal Report'[Signed Date] >= MIN('Calendar'[Date]) && 'Renewal Report'[Signed Date] <= MAX('Calendar'[Date]) ) ) VAR Renewals_Previous_FY = CALCULATE( COUNTROWS('Renewal Report'), FILTER( 'Renewal Report', 'Renewal Report'[FY] = 2023 && 'Renewal Report'[Signed Date] >= MIN('Calendar'[Date]) && 'Renewal Report'[Signed Date] <= MAX('Calendar'[Date]) ) ) VAR Total_Renewals = Renewals_Current_FY + Renewals_Previous_FY VAR Total_Entries = COUNTROWS('Renewal Report')
cheeto92
2 years ago Place DAX Commands and Tips
2.5KViews
0likes
12Comments
Split Text into rows using DAX
Hi power BI expert, I need to split into rows using DAX instead of using Power Query due to performance (the data is too large). For info, previously i was using Power Query but I'm keep getting error messages saying about performance. Below is my sample data: There is some calculation that i need to do once names splitted into rows. Really need anyone help to achieve this Thank you
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.1KViews
0likes
4Comments
Dax Formula Help - For given columns and filter value SUM another column
I have inputted the data set below and power bi report layout I would like to acheive Basically I need to know the DAX formula for SUM (VarGroup) for given PolicyNumber, WS Instance, penedid,Coverage Date, where status = Active Report PolicyNumber Cov Eff Date Auto Added Endorsement Code Endorsement Title Endorsement Effective Date Cancel Date Endorsement Expiry Date Signature Required Signed by Policyholder VarGroup GE 1 36131 September 1, 2017 No SC001 Exclusion July 1, 2015 No 5 GE 1 36131 September 1, 2016 No SC001 Exclusion July 1, 2015 No 5 GE 1 36131 September 1, 2015 No SC001 Exclusion July 1, 2015 No 5 GE 1 36131 September 1, 2014 No SC001 Exclusion July 1, 2015 No 5 GE 1 36131 September 1, 2014 No SC001 Exclusion September 1, 2014 June 30, 2015 No GE 1 36131 September 1, 2013 No SC001 Exclusion September 1, 2010 No 2 GE 1 36131 September 1, 2012 No SC001 Exclusion September 1, 2010 No 2 Data PolicyNumber Auto Added Endorsement Code Endorsement Title Endorsement Effective Date Cancel Date Cov Eff Date Endorsement Expiry Date Signature Required Signed by Policyholder Name Values WS Instance Pen ENDID Status Detail ID Variable ID VarGroup GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2014 No Excluded from ABC Tokyo 851110 4902931 Active 3183723 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2014 No Excluded from ABC Tsuk 851110 4902931 Active 3183721 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2014 No Excluded from ABC Towa 851110 4902931 Active 3183722 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2014 No Excluded from ABC Hanwa 851110 4902931 Active 3183724 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2014 No Excluded from ABC Jap 851110 4902931 Active 3183725 1187 1 GE 1 36131 No SC001 Exclusion September 1, 2014 June 30, 2015 September 1, 2014 No Excluded from ABC Hanwa 851110 4902930 Cancelled 3183718 1187 GE 1 36131 No SC001 Exclusion September 1, 2014 June 30, 2015 September 1, 2014 No Excluded from ABC Towa 851110 4902930 Cancelled 3183716 1187 GE 1 36131 No SC001 Exclusion September 1, 2014 June 30, 2015 September 1, 2014 No Excluded from ABC Tsuk 851110 4902930 Cancelled 3183715 1187 GE 1 36131 No SC001 Exclusion September 1, 2014 June 30, 2015 September 1, 2014 No Excluded from ABC Jap 851110 4902930 Cancelled 3183719 1187 GE 1 36131 No SC001 Exclusion September 1, 2014 June 30, 2015 September 1, 2014 No Excluded from ABC Tokyo 851110 4902930 Cancelled 3183717 1187 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2017 No Excluded from ABC Jap 100028 6570804 Active 4492710 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2017 No Excluded from ABC Tsuk 100028 6570804 Active 4492706 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2017 No Excluded from ABC Towa 100028 6570804 Active 4492707 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2017 No Excluded from ABC Tokyo 100028 6570804 Active 4492708 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2017 No Excluded from ABC Hanwa 100028 6570804 Active 4492709 1187 1 GE 1 36131 No SC001 Exclusion September 1, 2010 September 1, 2013 No Excluded from ABC Tsuk 765825 4014096 Active 2558828 1187 1 GE 1 36131 No SC001 Exclusion September 1, 2010 September 1, 2013 No Excluded from ABC towa 765825 4014096 Active 2558829 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2016 No Excluded from ABC Hanwa 995382 6510535 Active 4445863 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2016 No Excluded from ABC Tsuk 995382 6510535 Active 4445860 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2016 No Excluded from ABC Tokyo 995382 6510535 Active 4445862 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2016 No Excluded from ABC Towa 995382 6510535 Active 4445861 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2016 No Excluded from ABC Jap 995382 6510535 Active 4445864 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2015 No Excluded from ABC Tokyo 908778 5507017 Active 3664358 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2015 No Excluded from ABC Tsuk 908778 5507017 Active 3664356 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2015 No Excluded from ABC Towa 908778 5507017 Active 3664357 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2015 No Excluded from ABC Hanwa 908778 5507017 Active 3664359 1187 1 GE 1 36131 No SC001 Exclusion July 1, 2015 September 1, 2015 No Excluded from ABC jap 908778 5507017 Active 3664360 1187 1 GE 1 36131 No SC001 Exclusion September 1, 2010 September 1, 2012 No Excluded from ABC Tsuk 697648 3354307 Active 2111839 1187 1 GE 1 36131 No SC001 Exclusion September 1, 2010 September 1, 2012 No Excluded from ABC towa 697648 3354307 Active 2111840 1187 1
Solved
FahadQ
2 years ago Place DAX Commands and Tips
511Views
0likes
2Comments
dax help
Hi all, I'm trying to calculate 25*108075 = 2701875 instead I'm getting results as 2746186 which is incorrect. I have different categories columns in the table not sure how this is getting filtered, please help.
Solved
cool_333
3 years ago Place DAX Commands and Tips
726Views
0likes
1Comment
Power BI, DAX Help
Team, I have a large table in power bi that is pulled from SharePoint list. Below is how data look like. Here you can see employee name and the date when he completed the training. I need to get a table where only the latest date completed should be listed for each of the employee. Current State Emp Name Date Completed Name of the Training Joe 1-Jan-23 Compliance Training Joe 15-Jun-22 Compliance Training Joe 1-Jan-22 Compliance Training I need the data to show as below in power bi table. Future State Emp Name Date Completed Name of the Training Joe 1-Jan-23 Compliance Training
Solved
Lionel_Charley
3 years ago Place DAX Commands and Tips
636Views
0likes
2Comments
Help Flag on column
I am trying to create 2 measures one for flag a and another for flag b, and these measures have to change based on the date range filtered and the appearance of all two flags in the recent date. So in other words if I apply a filter from 25/02/2023 to 08/03/2023 as in the picture the output of the measure flag A must be 0 in all rows because in the last date 08/03/2023 it does not appear, it appears flag so in the measure flag B must be 1 in all rows because in 08/03/2023 it appears as in the picture. Result expected: I can get the flag to change dynamically but only on the last row not all rows of all two measures, I don't know if it is possible to do that with a measure. Thanks.
Youness99
3 years ago Place DAX Commands and Tips
837Views
0likes
1Comment
SUM and filter by month
Hi All, I'm new to DAX and am having some trouble with this measure: I need to SUM 3 columns from different tables, however when I apply a month filter to the page it does not filter the SUM value so I am looking for a solution to filter the measure by month. I have a date table and would like to filter by Month_Year. Total Budget = CALCULATE(SUM('Catering Budget'[Catering Budget]) + SUM('Dining Subsidy'[Dining Budget]) + SUM('Office Coffee Budget'[Office Coffee Budget])) Thanks in advance!
Solved
am_i_really
3 years ago Place DAX Commands and Tips
710Views
0likes
2Comments