@powerbi report. @dax

11 Topics

DAX Switch & nested IF
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-challenge-nested-if-or-switch-is-not-working/m-p/4792912#M183444 i can't belive there is no solution for this, i need a simple dax measure IF( ISINSCOPE( HIER[LEVEL_7_DESC] ), 17, IF( ISINSCOPE( HIER[LEVEL_6_DESC] ), 16, IF( ISINSCOPE( HIER[LEVEL_5_DESC] ), 15 ) ) ) this doesn't work because if i change the order of column in the visual then the higher or the first condtition will always be the only one is true returning this value only?!!
Solved
Maximous89
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
Total Customers (end of month)
Hi Everyone, Could you help me configure this? create a DAX measure that calculate the Total Customers (end of month) based on total customers at eriod of time and cancelled customers. Note: all metrics was calculated by measures. pbidaxhelp daxdax dax daxdaxdax
Solved
wyanjaspew
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
Default dynamic last 365 days between slicer in a page
Hello PowerBI, I have created a page with multiple visuals like bar, Line and Table and showing data according to my requiremet. Now the issue is I would liek to place a slicer so that it should show only last 365 days whenever I login. This can be achievable by relative date filter but the issue here is if I would like to go back to previous years the slicer is showing only last 365 days as I have applied filter for last 365 days. My concern here is Automatically show the last 1 year of data when the report is opened. Allow users to freely explore all available dates (e.g., from 2010 onwards) using a date slicer. Achieve this behavior using only calculated columns or measures (no bookmarks/buttons). Keep the default filter dynamic (so "last 1 year" means 1 year from today, even if it's opened tomorrow). If I would like to select other dates, the visuals should interact with filter accordingly. Approch I tried: I have created Bookmarks and buttons to show last 365 days and go back to full dates view so that we can be able to filter for the dates which ever we want. Need solution: Now I would like to achieve it using calculated columns or measure. or anyother simpler approach which we can do.
Solved
varundathi
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
Need to getting latest date rows only against duplicate dates and unique dates
Hi Team, I need your help to get the desired output in Power BI DAX. Below is my dataset: ProjectName RunId DateTime TotalTest CoveredLine TotalLine Sky 464545 09/19/2024 787 6753 7611 Sky 466518 10/03/2024 787 6753 7611 Sky 468720 10/18/2024 795 6837 7693 Sky 470858 11/05/2024 795 6844 7699 Sky 474794 12/02/2024 808 6933 7790 Sky 475678 12/06/2024 808 6932 7790 Sky 480882 01/16/2025 815 6974 7826 Spark 462901 09/11/2024 1246 0 0 Spark 463000 09/11/2024 1246 34712 52198 Spark 466141 10/01/2024 1246 34719 52254 Spark 467175 10/08/2024 1274 35037 52745 Spark 474420 11/28/2024 1270 35033 52737 Spark 474734 12/02/2024 1270 35033 52737 Spark 481680 01/23/2025 1304 35441 53393 Spark 481763 01/23/2025 1304 35441 53393 Spark 482047 01/24/2025 890 32354 48254 Spark 482154 01/24/2025 1304 35450 53402 Radiance 460937 08/29/2024 65 618 4404 Radiance 461169 08/30/2024 45 232 4404 Radiance 461227 08/30/2024 12 618 1547 Radiance 461229 08/30/2024 65 200 1100 Vision Venture 476179 12/10/2024 78 977 1629 Vision Venture 479004 01/03/2025 84 1242 1629 Vision Venture 479692 01/08/2025 84 1246 1641 I have a projectName filter on my report, and it is set for single selection only. I need to show the latest date data rows only in three different KPIs. See example below: In my dataset, for some projects, I have more than one same date [duplicate dates for last rows] for the month. In this scenario, we should consider only the last rows. For example: If I select the project "Sky," the result should be shown in the KPI against the latest date [01/16/2025]: Total Test: 815 Covered Line: 6974 Total Line: 7826 If I select the project "Spark," there are two entries for the last date [01/24/2025]. In this case, we should consider only the last rows, and the result should be shown in the KPI: Total Test: 1304 Covered Line: 35450 Total Line: 53402 If I select the project "Radiance," there are three entries for the last date [08/30/2024]. In this case, we should consider only the last rows, and the result should be shown in the KPI: Total Test: 65 Covered Line: 200 Total Line: 1100
Solved
AmitSaini
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
List of values with filter, kindly help me!
Hi Team, Kindly help me for filter visual. I have the 'Data' column and categories columns. like below snapshot format. we required filter, If I select categories col of 'a' then "a" related all data shown like below snapshot. If select 'b' then "b" related of all data required. How to it in DAX with this? Note:- In power query it is possible, by splitting muliple cols and we will do it. but have performacne impact, kindly help me to it using DAX?
Solved
Anonymous
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
Dax measure query required of below shared expressions. kindly help me!
Hi Team, Good Afternoon! Kindly help me for DAX measure query of below 2 expressions. 1. Sum([ABC] * [DEF] / 100) 2. Sum((case when [AAA]>1 then [AAA] / 100 else [AAA] end) * [XYZ]) / Sum((case when [BBB]>1 then [BBB] / 100 else [BBB] end) * [XYZ]) I required measures due to need to call this final values in the card visual. please help me.
Solved
Anonymous
1 year ago Place DAX Commands and Tips
Views
4likes
Comments
measure = AVERAGEX(DATEDIFF(DateA, DateB, DAY)) does not work.
Hi everybody, I have 2 colonnes says DateA and DateB with date format. If I create another C Column = DateA -DateB, it works. I have many average date difference to calculate, so I would like to create Measures instead of Columns. But I can't get this result. When I use Measure = datediff(, I can't use columns, I understand that it doesn't know what level I am, I need to use an agregation like min() ? Can someone please help me understand why it doesn't work. I want to make a calculation like : measure = AVERAGEX(DATEDIFF(DateA, DateB, DAY)) Thanks in advance,
Solved
BTRD
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
identifying active users every month
Hi, I have a list of users every month, and i need to find the active users in every month. maybe by creating a new calculated column which states if the user is active or not - If the user names present in the next available month file then those user names are active or - if a new user appears for the very first time which are not available in any of the month files then they are active from that month onwards. Please note if user names not appearing continuously then they are inactive. please find the data like below: column names are: System user name,Count of System user name,Sum of cost,Monthly Source file Name System user name Count of System user name Sum of cost Monthly Source file Name User_A 3 € 112.50 24-Jan User_A 3 € 112.50 24-Mar User_A 3 € 112.50 24-Apr User_A 3 € 112.50 24-May User_A 3 € 112.50 24-Jun User_A 3 € 112.50 24-Sep User_B 7 € 149.50 24-Mar User_B 7 € 149.50 24-Apr User_B 7 € 149.50 24-May User_B 7 € 149.50 24-Jun User_B 8 € 238.50 24-Sep User_C 5 € 139.50 24-Jan User_C 7 € 151.50 24-Mar User_C 7 € 151.50 24-Apr User_C 7 € 151.50 24-May User_C 7 € 151.50 24-Jun User_C 7 € 151.50 24-Sep User_D 12 € 323.00 24-Jan User_D 13 € 329.00 24-Mar User_D 13 € 329.00 24-Apr User_D 13 € 329.00 24-May User_D 13 € 329.00 24-Jun User_D 14 € 332.00 24-Sep User_E 5 € 138.50 24-Jan User_E 7 € 209.50 24-Mar User_E 7 € 209.50 24-Apr User_E 9 € 222.50 24-May User_E 9 € 222.50 24-Jun User_E 9 € 305.00 24-Sep User_F 11 € 362.50 24-Jan User_F 13 € 374.50 24-Mar User_F 14 € 380.50 24-Apr User_F 14 € 380.50 24-May User_F 14 € 380.50 24-Jun User_F 14 € 463.50 24-Sep User_G 6 € 219.50 24-Apr User_G 7 € 226.00 24-May User_G 6 € 219.50 24-Jun User_G 7 € 246.50 24-Sep User_H 3 € 112.50 24-Mar User_H 3 € 112.50 24-Apr User_H 3 € 112.50 24-May User_H 3 € 112.50 24-Jun User_H 3 € 112.50 24-Sep How do i create this Active users list every month using the above data in power bi?? Thanks in advance for the help
Solved
Bu__
1 year ago Place DAX Commands and Tips
Views
0likes
Comments
One Slicer Selection Filters Another Slicer Selection
Hello Power BI Community, I have two columns which have their own slicers on the dashboard: AZURE_AUDIT_METRICS[Object] AZURE_AUDIT_METRICS[Segment] When a team member selects a certain Data Object I would like the Segment slicer to filter to a specific Segment automatically. One Object can have many Segments so we want to highlight (filter for) the most important Segment right away. This is best explained with an example.... The user selects Data Object slicer AZURE_AUDIT_METRICS[Object] = "PRA UNIT VENTURE". When this selection is made there are two Segment slicer AZURE_AUDIT_METRICS[Segment] selections available (see screenshot). We want the Segment slicer AZURE_AUDIT_METRICS[Segment] = "UNITVENTURE PUC" to be automatically selected by default when Data Object slicer AZURE_AUDIT_METRICS[Object] = "PRA UNIT VENTURE" is selected. Thank you so much for your assistance!! Brenden
Solved
BrendenS
2 years ago Place DAX Commands and Tips
Views
0likes
Comment
Cumulative Filter for previous values
Hello folks, I am newbiew in Power BI, and now I am struggiling to create a formula for filtering. This would be my data set example: Country Mark Minutes AA aa11 30 AA aa22 60 BB bb88 120 BB bb00 180 CC cc99 380 DD dd55 480 DD dd45 550 EE ee98 1440 EE ee33 1520 Now, I would need to create a filter like this: IF Minutes < 120, "< 2 hrs", if minutes < 240, "< 4 hrs", if minutes < 480, "< 8 hrs", if minutes < 960, "< 16 hrs", if minutes < 1440, "< 1 day", if minutes >= 1440, "> 1 day" But, if I select filter "< 8 hrs" I would like to include all values for "< 8 hrs", "< 4 hrs" and "< 2 hrs". Also, if I filter e.g. "< 4 hrs", I would need to include values for "< 4 hrs" and "< 2 hrs", and so on and so forth. I tried several solutions, like first I created a calculated column: NumericCategory = SWITCH( TRUE(), 'Table1'[Minutes] < 120, 1, // "< of 2 hrs" 'Table1'[Minutes] < 240, 2, // "< of 4 hrs" 'Table1'[Minutes] < 480, 3, // "< of 8 hrs" 'Table1'[Minutes] < 960, 4, // "< of 16 hrs" 'Table1'[Minutes] < 1440, 5, // "< of 1 Day" 'Table1'[Minutes] >= 1440, 6 ) Then I created additinal calculated column: TextCategory = SWITCH( [NumericCategory], 1, "< 2 hrs", 2, "< 4 hrs", 3, "< 8 hrs", 4, "< 16 hrs", 5, "< 1 day", 6, "> 1 day" ) And finally, I created a new measure: CumulativeFilter = VAR SelectedTextCategory = SELECTEDVALUE(Table1[TextCategory]) VAR MaxNumericCategory = SWITCH( SelectedTextCategory, "< 2 hrs", 1, "< 4 hrs", 2, "< 8 hrs", 3, "< 16 hrs", 4, "< 1 day", 5, "> 1 day", 6 ) RETURN CALCULATE( SUM(Table1[Minutes]), FILTER( Table1, Table1[NumericCategory] <= MaxNumericCategory ) ) And when I use "TextCategory" as a slicer and select e.g. "< 4 hrs" it filtered me only the records that are fitted for this condition. It won't me include the values for "< 2 hrs". Any idea how to achive this? Thank you in advance.
Solved
exe_binary
2 years ago Place DAX Commands and Tips
Views
0likes
Comments