dax help needed

77 Topics

DAX Command - IF variables, SWITCH and COALESCE
I need a DAX commend to identify the store to which an employee is based to sales commission calculation purposes Please find table of specific scenarios and expected results for each one. So I have parts of the DAX command however need some help combining. IF( OR( Entrance Store = "Default Store", 'Exit Store = "Defailt Store") ), "Default Store" VAR Entrance = [EntranceStore] VAR Exit = [ExitStore] VAR Default = [DefaultStore] RETURN SWITCH( TRUE(), ISBLANK(Entrance) || ISBLANK(Exit), Default, NOT ISBLANK(Entrance) && Entrance <> Default, Entrance, NOT ISBLANK(Exit) && Exit <> Default, Exit, Default ) Entrance Store Exist Store Default Store Result Store Comments IL213 IL213 IL213 IL213 IL213 IL213 IL213 IL213 IL213 IL135 IL213 IL213 IL135 IL213 IL135 IL135 IL135 IL135 IL213 IL213 IL135 IL213
Solved
khisla
1 year ago Place DAX Commands and Tips
841Views
0likes
5Comments
Calculate top 6 clients in descending order
Hi I am hoping you can help me. I need to get a list of top 6 clients based on the highest sales volume Secondly I want to get an average of the volume of these clients over the total client volume by sales Thanks G
Solved
gra_197
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
6Comments
Sum of $ Amount Totals Over Multiple Years
Hello Community - Any help on this DAX calculation would be greatly appreciated. The PBIX is shared here. https://drive.google.com/file/d/1BNmF6x3XW1oCaONl9Mz8KY8uEw2fD_dS/view?usp=drive_link Currently, the report is working as desired, except for 1 measure. I am unable to successfully calculate measure [01D Premium YTM Adj - Mult Years]. Important Notes for Table Visual "Summary - Main": - Table Visual is filtered to only include records WHERE [T1&T2] = 1. - [T1] returns 1 only if "Avg Loss Ratio" > "Program Enrollment %" slicer selected value. - [T2] returns 1 only if "Loss Ratio YTM" > "Loss Ratio Trigger %" slicer selected value. - "Adtl Loss Adj Exp" column is calculated using the "Additional Loss Adjustment Expense" slicer selected value. - Both " Loss Ratio YTM" & "Premium YTM" are calculated based on the "Year to Model" slicer selected value. Desired Outcome #1: The top right card visual needs to correctly display the Total "Adtnl Loss Adj Exp" across multiple selected years. - This can either be a static number of years (e.g. 10 years hard-coded into the measure) - OR it should be the selected years in the "Year" slicer For example, if I keep slider slicer values constant (150%, 100% & 10% respectively) and only change "Year to Model", the correct total $ for each YTM (Year to Model) is as follows: 2021 = $30,835,812 2022 = $78,723,649 2023 = $71,350,756 2024 = $69,025,807 The new card visual needs to TOTAL all of these $ values over all the selected Years (or hard-coded years) in the "Year" slicer. For example, if the Year slicer theoretically had only these 4 years selected, then the card visual needs to show the SUM of these 4 years. $30M + $78M + $71M + $69M = $249.9M NOTE: What's confusing here is that the "Year to Model" slicer is being used to correctly calculate multiple columns in the "Summary - Main table", which then impacts which rows are included/excluded, and thereby impacts the total $ amount calculation. However, this new card visual needs to SUM up these same YTM $ amounts for every year in the "Year" slicer (or a hard-coded # of years in the measure). If ALL years are selected in the "Year" slicer, then the card should show the total $ amount for all years. Desired Outcome #2: The same calculation in the new card visual above needs to be broken out in a table visual by Year. Once again, in the example below, I'm pretending that only 4 years are selected (2021-2024). However, we need to do this: - Either across all years selected in the "Year" slicer - OR by a hard-coded number of years in the measure (e.g. last 10 years) Very thankful for any help on this one, Nathan
Solved
WinterMist
1 year ago Place DAX Commands and Tips
3.4KViews
0likes
16Comments
Request for Best-Practice-Approach, filtering complete page / report based on edited Measure
Hello there, I would like to comfort my customer with the following functionality: He should beable to edit a Measure called "PresentationDate" either hardcoded or with slicer. On that date a meeting takes place to show figures actual status vs 2 month before. The plan is to let him edit the measure (e.g. May, 11th 2025 = 11.05.2025 ) and all filters / slicers on the page(s)/ the report will addjust correctly based on that date. Do you have or know any sources handling that kind of functionality / approach which you can share? Thank you very much, have a nice easter weekend. Thomas
Solved
thomasreick
1 year ago Place DAX Commands and Tips
776Views
0likes
2Comments
DAX Measure: Incorrect Total of PBI vs Excel export
Hello Power BI Community, I'm facing an issue with my DAX measure Budget Balance, where the totals in Power BI visual don't match when exported the results to Excel. The row-level calculations seem fine, but the total is incorrect. Here’s my current measure trace: Due = [ParticipationAmt] - [ReceivedAmt] OpenAging = CALCULATE([Due], Event[Event Age Group] <> "Paid/Completed", REMOVEFILTERS(Event[Event Date]), Event[Event Age] > 0) FlatRatesTotal = IF(ISINSCOPE('Branch'[FPA Name]) || ISINSCOPE('Branch'[RM Name]) || ISINSCOPE('Branch'[Branch]), BLANK(), SUM('Flat Rates'[Flat Rates])) Total YTD Budget = [ActualBudgetTotal] + [FlatRatesTotal] Budget Balance = IF([Total YTD Budget] = 0, [OpenAging]*-1, [Total YTD Budget] - SUM('Transaction Detail'[Participation/Received])) Calculated Column: Participation/Received = IF(LOOKUPVALUE(Event[Event Status], Event[Event ID], 'Transaction Detail'[EVENTID])="Closed", [Received $], [Participation $]) The issue: The total for Budget Balance does not align with Excel (PBI totals are lower compared to Excel) I suspect the issue may be with SUM vs. iterating functions like SUMX or how totals are aggregating at different levels. Can anyone help identify what might be causing the incorrect total and how to fix it? Appreciate any insights! Thanks in advance!
Solved
datajunkie_29
1 year ago Place DAX Commands and Tips
1KViews
0likes
5Comments
Adding column from seperate table to unique combination
Hi Community i am facing an issue with updating my DAX measure. Below you will find the current code which intends to count the number of unique combinations of variant item no and variant code where there margin is between 10-25%, and it works just fine for the current setup in my report. However, the business requirements have changed a bit, so it also needs to contain the combination of sales person. So now a three part unique combination. Both Variant item no and code is stored in a table called "Variant", though the information for sales person is stored in a seperate table, and i cant seem to figure out how to perform this task with my code today, since it wont allow me to add more than one table reference (Variant) and so i cant add Sales person like this: Variant[Variant Item No], Variant[Variant Code], Sales Person[Sales Person Name] Current code: # Count of Margin between 10-25% = CALCULATE( COUNTROWS( SUMMARIZE( FILTER( Variant, NOT(ISBLANK([Invoiced Amount Incl All Charges])) && [Gross Margin 2 %] > 0.10 && [Gross Margin 2 %] <= 0.25 ), Variant[Variant Item No], Variant[Variant Code] ) ) ) Im not sure if this matters in this context, but my connection is a tabular model through analysis services. Any ideas how to update my code or perhaps change it entirely based on my requirements? Thanks!
Solved
Nicpet0
1 year ago Place DAX Commands and Tips
647Views
0likes
3Comments
Conditionally Select A Table give me error message
I created two tables one is for All divisions/ entire organization and one for divisions when one division is selected. I have a variable called DivisionFilter using HASONEVALUE to detect if a division is selected. if nothing is selected, I want to use the All division table to do a Top 10, otherwise I will use the division table to show divisional Top 10. When I use if statement below, I got a error message saying "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". How do I fix it. Thanks. IF( DivisionFilter, FILTER(RankedDivision, [RankByBL] <= 10), // Top 10 within each division if filtered TOPN(10, RankedAll, [Backlog], DESC) // Top 10 for the entire organization if not filtered )
Solved
jimmyhua
1 year ago Place DAX Commands and Tips
1KViews
2likes
6Comments
Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division
Hi All, I try to create a dax to pull top 10 projects with backlogs by division and by month. below is the dax. I got an error message saying cannot convert Division name to T/F logic. what could be wrong? VAR DivisionMonthTable = ADDCOLUMNS( SUMMARIZE( PJ401, PJ401[Div Name], PJ401[Date], PJ401[Project ID], PJ401[Project Name], "Backlog", CALCULATE(SUMX(PJ401, [CM CV]*[CV%]-[Inception-to-date Revenue ]), KEEPFILTERS( PJ401[Div Name]),KEEPFILTERS( PJ401[Date])) ), "RankByBL", RANKX( All(PJ401[Project ID]), [Backlog], , DESC, Dense ) ) RETURN FILTER( DivisionMonthTable, [RankByBL] <= 10 )
Solved
jimmyhua
1 year ago Place DAX Commands and Tips
1.2KViews
0likes
6Comments
Urgent Help on DAX - Previous Year/Months data on dates
Hi All, Please help on the DAX, below is the screenshots and sample data pasted in the content. I have tried so many approaches, but unable to reach OUTPUT in Power BI. Please help. INPUT: Here is the screenshot of data coming from database loading into the Power BI desktop. OUTPUT: Here is the screenshot of OUTPUT layout to Visualize in "Table Visual". CALCULATION: Here is the condition/logic need to work on DAX. Example: If Application date is 2023-10 (01): Month of Extraction date - 12 month (2023/October - 12 months ⇒ 2022/October) (01)Data of 2022-10(12month ago of Extraction date) (02)Data of 2022-11(11month ago of Extraction date) (03)Data of 2022-12(10month ago of Extraction date) (04)Data of 2023-01(9month ago of Extraction date) (05)Data of 2023-02(8month ago of Extraction date) (06)Data of 2023-03(7month ago of Extraction date) (07)Data of 2023-04(6month ago of Extraction date) (08)Data of 2023-05(5month ago of Extraction date) (09)Data of 2023-06(4month ago of Extraction date) (10)Data of 2023-07(3month ago of Extraction date) (11)Data of 2023-08(2month ago of Extraction date) (12)Data of 2023-09(1month ago of Extraction date) (13)Data of 2023-10(0month ago of Extraction date) SAMPLE DATA: Here is the sample data to work and load the data in Power BI. Direct copy the data and paste it in Excel Earliest APPL_CTL_PRCES_DT POLICY_7DIGIT BUYING_YEARS_MONTHS INCREASED_LIVING_BENEFIT CUMULATIVE_INCREASED_SURVIVAL_BENEFITS 2023-10-12 8514723 2022-10-01 4 98030 2023-10-12 8514723 2022-11-01 4 105034 2023-10-12 8514723 2022-12-01 4 112039 2023-10-12 8514723 2023-01-01 4 119044 2023-10-12 8514723 2023-02-01 5 126049 2023-10-12 8514723 2023-03-01 5 133055 2023-10-12 8514723 2023-04-01 5 140060 2023-10-12 8514723 2023-05-01 6 147066 2023-10-12 8514723 2023-06-01 6 154073 2023-10-12 8514723 2023-07-01 6 161080 2023-10-12 8514723 2023-08-01 6 168087 2023-10-12 8514723 2023-09-01 7 175094 2023-10-12 8514723 2023-10-01 7 182101 2017-06-01 6951290 2016-06-01 0 0 2017-06-01 6951290 2016-07-01 0 0 2017-06-01 6951290 2016-08-01 0 0 2017-06-01 6951290 2016-09-01 0 1 2017-06-01 6951290 2016-10-01 0 1 2017-06-01 6951290 2016-11-01 0 1 2017-06-01 6951290 2016-12-01 0 2 2017-06-01 6951290 2017-01-01 0 2 2017-06-01 6951290 2017-02-01 0 2 2017-06-01 6951290 2017-03-01 0 2 2017-06-01 6951290 2017-04-01 0 3 2017-06-01 6951290 2017-05-01 0 3 2017-06-01 6951290 2017-06-01 0 3 2020-06-12 6951290 2020-06-12 6951290 2019-07-01 0 3 2020-06-12 6951290 2019-08-01 0 3 2020-06-12 6951290 2019-09-01 0 3 2020-06-12 6951290 2019-10-01 0 3 2020-06-12 6951290 2019-11-01 0 3 2020-06-12 6951290 2019-12-01 0 3 2020-06-12 6951290 2020-01-01 0 3 2020-06-12 6951290 2020-02-01 0 3 2020-06-12 6951290 2020-03-01 0 3 2020-06-12 6951290 2020-04-01 0 3
Anonymous
1 year ago Place DAX Commands and Tips
1.3KViews
0likes
6Comments
Calculating difference between 2 rows of data based on date and movement
Hello I'm trying to work out in Dax how to calculate the difference in value between 2 dates with an added variance. So in essence the difference for 01/07/2024 is Value for 02/07/2024 - (Value for 01/07/2024 + Transactions for 01/07/2024) I just can't wrap my head around the DAX Date Value Transactions Difference 01/07/2024 50000 1000 1000 02/07/2024 52000 3500 1500 03/07/2024 57000 3000 -1000 04/07/2024 59000 1500 -500 05/07/2024 60000 500
Solved
SarahESkells
2 years ago Place DAX Commands and Tips
889Views
0likes
3Comments