dax query

79 Topics

Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)
Hi everyone, I have two tables in my Power BI model: 1. Fact table – contains columns like Country, State, City, Pincode, Product Name, Category, etc. 2. Sales table – contains Sales Value and Product Name,date,net profit I want to create a logic based on Sales Value bands: Top 50% → Green Next 20% → Yellow Remaining 30% → Red Here’s the requirement: When I select any filter such as Country or Pincode, I only want to calculate the max sales value for that selected filter and not for all filters combined. For example, if I select a specific Pincode, it filters 100 rows from my 10,000-row dataset. Among those 100 filtered rows, I want the top 50% to fall under Green, next 20% under Yellow, and the last 30% under Red dynamically based on filter selection. I already tried writing a DAX measure, but it’s not giving the expected result. The calculation seems to ignore the filter context and applies across the entire dataset. Could anyone please help me with the correct DAX logic to achieve this? Thanks in advance!
Solved
Anandshah
9 months ago Place DAX Commands and Tips
1KViews
3likes
6Comments
Dax Script
hello all, i'm wondering if someone can help m, being trying to make this measure since the last 1 week now my client asked me to produce a dashboard displaying only the last message received here is the scenario. its a reservation system, users have id call PDL and eache PDL can have one or multiple message when booking an appointement. types of message: - Prise de rdv en succès -proposition possible pas de possibilité de rdv etc.. now the mesure that i need to create is the count of all last message per max(hour). i made the following script test_mesure:=CALCULATE( DISTINCTCOUNT('ANALYSE PRV MESURES'[PDL]); LASTNONBLANK('ANALYSE PRV MESURES'[DATETIME];'ANALYSE PRV MESURES'[DATETIME]) ) and this is not working. someone has an idea on how to make it ?
Anonymous
10 months ago Place DAX Commands and Tips
2KViews
0likes
2Comments
Dax Formula not working
CMLYNAACount = VAR SelectedMonth = MAX(Query1[CSMDate]) VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -12) + 1 VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12) RETURN CALCULATE( SUM(Query1[NACCount]), Query1[CSMDate] >= LastYearSameMonthStart && Query1[CSMDate] <= LastYearSameMonthEnd ) I am trying to get a count based on a filter however, i am not getting any results on this query. Can you advise whats wrong? I am trying to get NAC Count based off the date slicer for last year current month . e.g if slicer says november 2024 this field should show november 2023
Solved
homelander123
1 year ago Place DAX Commands and Tips
839Views
0likes
3Comments
Stripping out the query string in a URL returning blank
Hello, I am trying to strip out everything including and after the ? from my url [page_location] to report on page usage, I am also stripping out my own website address to shorten the result. eg in the below random website example url https://ortc.com.au/products/logo-quarter-zip-charcoal?variant=41124690722934 all that would be left is /products/logo-quarter-zip-charcoal My query, which is a result partly of these forums and our chatgpt friend, is below but keeps returning blanks for every result. If anyone has any ideas I would be extremely grateful! Page Path = VAR Destringed_Page_Loc = IF( CONTAINSSTRING(Query1[page_location], "?"), LEFT(Query1[page_location], SEARCH("?", Query1[page_location]) - 1), Query1[page_location] ) RETURN SUBSTITUTE(Destringed_Page_Loc, "https://www.myurl.com.au", "")
Solved
L25
1 year ago Place Report Server
804Views
2likes
1Comment
Dax query that calculate InWork hours excluding weakeneds
i wrote the following dax query that calculate work hourse 8am to 5 pm however i need it to exclude the weekend (exclude saturday and friyday) please help for example this is how it currenlty is being calculatined start date end date work hourse 4/22/2022 3:00 4/23/2022 14:04:00 15 this is how i want it to be start date end date work hourse 4/22/2022 3:00 4/23/2022 14:04:00 0 and here is the query workhours new = -- Working Start and End time VAR WorkTimeStart = TIME ( 08, 00, 00 ) VAR WorkTimeEnd = TIME ( 17, 10, 10 ) VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart ) -- Start and End date/time on current row VAR StartingDateTime = [start date] VAR EndingDateTime = [end date] VAR StartingTime= StartingDateTime - TRUNC ( StartingDateTime ) VAR StartingDate = StartingDateTime - StartingTime VAR EndingTime = EndingDateTime - TRUNC ( EndingDateTime ) VAR EndingDate = EndingDateTime - EndingTime -- Adjust start/end times to fall within working hours. VAR StartingTimeEffective = MIN ( MAX ( StartingTime, WorkTimeStart ), WorkTimeEnd ) VAR EndingTimeEffective = MAX ( MIN ( EndingTime, WorkTimeEnd ), WorkTimeStart ) -- Adjust for hours not worked on StartingDate -- StartingTimeOffset will always be <= 0 VAR StartingTimeOffset = WorkTimeStart - StartingTimeEffective -- Adjust for hours not worked on EndingDate -- EndingTimeOffset will always be <= 0 VAR EndingTimeOffset = EndingTimeEffective - WorkTimeEnd VAR DayCount = EndingDate - StartingDate + 1 VAR TotalTimeInDays = DayCount * WorkingHours + StartingTimeOffset + EndingTimeOffset VAR TotalTimeInHours = TotalTimeInDays * 24 RETURN TotalTimeInHours
Solved
nony97
1 year ago Place DAX Commands and Tips
1.6KViews
0likes
9Comments
NewBie - Create new column that fills blank dates based on matching "order numbers"
Hi All, Very new to PowerBi here, covering a colleague who is out sick. Based on the table below, the table name is: 'OnlineChannel'. I am trying to create a new column based on the column "Scheduled Date" below, the new column will be named "Verified Scheduled Date". The new Column should have no blank values if the "Order Number" has a match in another row with a scheduled date. For example, Order Number "20246789" appears 3 times on the table, in lines 8, 9, and 10. The "Scheduled Date" value in line 10 is not blank, therefore all values for Order Number "20246789" in lines 8, 9, and 10 in the new "Verified Scheduled Date" column should match the existing date in line 10. I have no idea where to start, please help. Thanks in advance! Line Order Number Scheduled Date 1 12395969 1/1/2025 2 14959688 12/6/2024 3 14959688 4 18495768 9/1/2025 5 20241367 6 20241367 1/1/2025 7 20243959 5/15/2025 8 20246789 9 20246789 10 20246789 6/18/2025
Solved
Anonymous
2 years ago Place DAX Commands and Tips
565Views
0likes
2Comments
averages help
I have a table with 22,000 rows of data in a category I have a list "Category" , "reported Date" that's it. 22,000 in my list there are 83 different category's in my list. try to get Average per year and month. i don't know what i am doing wrong. thank you Category Reported date Clinical equipment / consumables 26/05/2022 Documentation / health records 20/12/2022 Information Governance 21/02/2022 Communication 24/06/2022 Documentation / health records 16/09/2022 Verbal Abuse 29/12/2022 Actual Physical Assault 12/09/2022 Systems of work 26/09/2022 Breach of policy / protocol / procedure 05/10/2022 Patient journey 07/03/2022 Discharge issues/concerns 05/10/2022 Discharge issues/concerns 14/09/2022 Breach of policy / protocol / procedure 14/09/2022 Breach of policy / protocol / procedure 07/12/2022 Speech and Language Therapy 04/10/2022 Flood 24/10/2022 Flood 24/10/2022 Treatment / procedure 06/11/2022 Moisture Associated Skin Damage 04/09/2022 Breach of policy / protocol / procedure 18/11/2022
Solved
Mark8
2 years ago Place DAX Commands and Tips
381Views
0likes
1Comment
DAX query with date parameter
Using Report Builder, I created a dataset that contains a date parameter. I need to add an order by to the DAX Query but am unsure of the correct syntax. Example is below. I need to order by 'Task'[ActivityDate] DESC DEFINE VAR vFromTaskActivityDateHierarchy1 = IF(PATHLENGTH(@FromTaskActivityDateHierarchy) = 1, IF(@FromTaskActivityDateHierarchy <> "", @FromTaskActivityDateHierarchy, BLANK()), IF(PATHITEM(@FromTaskActivityDateHierarchy, 2) <> "", PATHITEM(@FromTaskActivityDateHierarchy, 2), BLANK())) VAR vFromTaskActivityDateHierarchy1ALL = PATHLENGTH(@FromTaskActivityDateHierarchy) > 1 && PATHITEM(@FromTaskActivityDateHierarchy, 1, 1) < 1 VAR vToTaskActivityDateHierarchy1 = IF(PATHLENGTH(@ToTaskActivityDateHierarchy) = 1, IF(@ToTaskActivityDateHierarchy <> "", @ToTaskActivityDateHierarchy, BLANK()), IF(PATHITEM(@ToTaskActivityDateHierarchy, 2) <> "", PATHITEM(@ToTaskActivityDateHierarchy, 2), BLANK())) VAR vToTaskActivityDateHierarchy1ALL = PATHLENGTH(@ToTaskActivityDateHierarchy) > 1 && PATHITEM(@ToTaskActivityDateHierarchy, 1, 1) < 1 EVALUATE SUMMARIZECOLUMNS('Task'[WhatId], 'Task'[Status], 'Task'[Description], 'Task'[ActivityDate], FILTER(VALUES('Task'[ActivityDate]), (vFromTaskActivityDateHierarchy1ALL || 'Task'[ActivityDate] >= DATEVALUE(vFromTaskActivityDateHierarchy1) + TIMEVALUE(vFromTaskActivityDateHierarchy1)) && (vToTaskActivityDateHierarchy1ALL || 'Task'[ActivityDate] <= DATEVALUE(vToTaskActivityDateHierarchy1) + TIMEVALUE(vToTaskActivityDateHierarchy1)))) Any assistance is appreciated.
M_K_T
2 years ago Place Report Server
750Views
0likes
2Comments
Problem with visual plot (Dax Query)
Hello , I have problem with visual , I don't understand why visual shows a wrong .. . In Dad query total is correct, but I couldn't plot graph. On photo I show my dax query and windows with wrong information Pls help
adii
2 years ago Place DAX Commands and Tips
513Views
0likes
2Comments
How to multiply two different Tables Dax Query
Hi I have this 2 tables that I want to multiply with 3 filters. Year : Market Year Carrier: First Table 1 RVUYear CPT MOD CPTmod descrip workRVU NonFacTot FacTot YearCPTmod 2022 27299 NA 27299 Pelvis/hip joint surgery 0.00 0.00 0.00 2022 - 27299 2022 25999 NA 25999 Forearm or wrist surgery 0.00 0.00 0.00 2022 - 25999 2022 27599 NA 27599 Leg surgery procedure 0.00 0.00 0.00 2022 - 27599 2022 26989 NA 26989 Hand/finger surgery 0.00 0.00 0.00 2022 - 26989 2022 27899 NA 27899 Leg/ankle surgery procedure 0.00 0.00 0.00 2022 - 27899 2022 0520F NA 0520F Rad dos limts b/4 3d rad 0.00 0.00 0.00 2022 - 0520F 2022 0520T NA 0520T Rmvl&rplcmt pg wcs new eltrd 0.00 0.00 0.00 2022 - 0520T 2022 0521F NA 0521F Plan of care 4 pain docd 0.00 0.00 0.00 2022 - 0521F 2022 0521T NA 0521T Interrog dev eval wcs ip 0.00 0.00 0.00 2022 - 0521T 2022 21088 NA 21088 Prepare face/oral prosthesis 0.00 0.00 0.00 2022 - 21088 2022 1034F NA 1034F Current tobacco smoker 0.00 0.00 0.00 2022 - 1034F 2022 21089 NA 21089 Prepare face/oral prosthesis 0.00 0.00 0.00 2022 - 21089 2022 1035F NA 1035F Smokeless tobacco user 0.00 0.00 0.00 2022 - 1035F 2022 0522T NA 0522T Prgrmg dev eval wcs ip 0.00 0.00 0.00 2022 - 0522T 2022 1036F NA 1036F Tobacco non-user 0.00 0.00 0.00 2022 - 1036F 2022 1038F NA 1038F Persistent asthma 0.00 0.00 0.00 2022 - 1038F 2022 1039F NA 1039F Intermittent asthma 0.00 0.00 0.00 2022 - 1039F 2022 0523T NA 0523T Ntrapx c ffr w/3d funcjl map 0.00 0.00 0.00 2022 - 0523T 2022 1040F NA 1040F Dsm-5 info mdd docd 0.00 0.00 0.00 2022 - 1040F 2022 0524T NA 0524T Ev cath dir chem abltj w/img 0.00 0.00 0.00 2022 - 0524T Table 2 ServiceCenterRegion CarrierName PlanName CPT MOD CPTmod nCh sumUnits sumVolRVU sumValExp sumAmtCh sumAmtApprv DateTimeUpdate Proposed RVU Volume YearCPTmod Portland Metro Cigna 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 99024 Portland Metro Providence 2028F 2028F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 2028F Portland Metro Regence (BCBS) 4000F 4000F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 4000F Portland Metro UHC 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 99024 Southern Willamette Providence 1101F 1101F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 1101F Southern Willamette Regence (BCBS) 1101F 1101F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 1101F Southern Willamette UHC 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2020 - 99024 Central OR Cigna J0588 J0588 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - J0588 Central OR Regence (BCBS) A4450 A4450 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - A4450 Mid-Willamette Regence (BCBS) 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Northeast OR Providence 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Northeast OR UHC 99499 99499 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99499 Portland Metro Aetna 2028F 2028F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 2028F Portland Metro MODA 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Portland Metro Regence (BCBS) 2028F 2028F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 2028F Portland Metro Regence (BCBS) 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Portland Metro UHC 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Southern Willamette Aetna 1100F 1100F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1100F Southern Willamette Aetna 3288F 3288F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 3288F Southern Willamette Cigna 99024 99024 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 99024 Southern Willamette First Choice 1100F 1100F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1100F Southern Willamette Health Net G8427 G8427 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - G8427 Southern Willamette MODA 1100F 1100F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1100F Southern Willamette Providence 3072F 3072F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 3072F Southern Willamette Regence (BCBS) 1100F 1100F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1100F Southern Willamette Regence (BCBS) 3072F 3072F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 3072F Southern Willamette Regence (BCBS) G8482 G8482 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - G8482 Southern Willamette UHC 1101F 1101F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1101F Southern Willamette UHC 1220F 1220F 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - 1220F Southern Willamette UHC G8482 G8482 1 2.00 0.00 0.00 0.00 0.00 07/07/2023 17:27 2 2021 - G8482 not all the data where there. But that is the data structure.
Solved
JuradoKevin14
2 years ago Place DAX Commands and Tips
1KViews
0likes
2Comments