@need help

33 Topics

Eliminating zero values from Matrix visual
I have a matrix that shows three measures Billed_Hours, Unbilled_Hours, and Total_Hours for each project project. The formula for the measure Billed_Hours is : Billed_Hours = var BilledHr= calculate(sum(project_time[hours]) , project_time[is_billed]=TRUE ) Return switch(true, chgHr = 0,0, BilledHr) Unbilled_Hours measure is the opposite of the Billed_Hours. There are some projects with both billed and unbilled Hours equal to zero for whatever reason and I want to eliminate those projects from the matrix using a Dax measure that verifies if billed and unbilled hours are zero (it can check the Total Hours calculated column of the table as well) and eliminate these projects from the visual. Is it possible? (Note: For now, I got this done by putting in a measure in the filter pane that checks for ISEMPTY(project_time) and returns 1 or 0 but I would like to know how to do this using Dax. Would appreciate your help)
Solved
arunbyc
1 year ago Place DAX Commands and Tips
2.3KViews
0likes
3Comments
Filtering a table using TOP N slicer
Hi, i have a TOP N slicer which is a what if parameter from 0 to 50, i am trying to filter TOP N qty for a product in a table. please note there is already a TOPN filter on week for the table visual like pic below, so another topn is not possible and the data does not have an id/unique column and the products are repeted several times and the week in the sample is different from the topn loaddateweek. I am using a DAX measure to calculate the top products TOP N measure = VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Still to supply_sum], RANKX( ALLSELECTED(table[week],table[Product]), [Still to supply_sum], ,DESC,Dense ) <= SelectedTop, [Still to supply_sum] ) but this is not filtering for only 1 value when top n filter selected =1, but shows multiple value. Sample data is like below: Week Product Still to supply_sum rank 2024 34 6708 58720 1 2024 40 7706 64919 1 2024 38 6708 48120 2 2024 40 6708 43040 2 2024 38 731 34400 3 2024 36 6708 33560 3 2024 39 7448 36000 3 2024 41 6961 32040 5 2024 38 300 30080 6 2024 36 300 28120 7 2024 30 7551 24720 9 2024 41 122116010 22160 10 2024 36 731 21560 11 2024 41 7551 19790 15 2024 32 7551 19168 17 2024 31 300 16204 24 2024 32 300 15669 25 Not sure how to solve this issue to just have rank that does not repeat for a another row for the same product. Thanks for the help in advance.
Solved
Bu__
1 year ago Place DAX Commands and Tips
1.2KViews
0likes
3Comments
Need help Calculate Prior year values working for one other filter
Hi , I am working on the data where I am calculating the sum of appointments total by prior fiscal year. to calculate the prior year values , I have used a calculation suggested by this forum, it works fine when we select year filter, but the moment I select other filter this calculation prior year calculation doesnot hold good. How do I correct it ? This is the calculation I am using to calculate previous year calculate(sum('OLD ONE'[COUNT(DISTINCT APT)]),filter(ALL('OLD ONE'),[FISCAL_YEAR_NUM]=SELECTEDVALUE('OLD ONE'[FISCAL_YEAR_NUM])-1)) Now I want this calcualtion to work for other region level filter too . the moment I chose a region filter the prior year filter is not working
Solved
MaliniBaktha
2 years ago Place DAX Commands and Tips
595Views
0likes
2Comments
Issue with Displaying Correct Subtotals in Matrix Visual
Hello, I'm seeking assistance with a matrix visual in Power BI where I need to use a specific column as a value without aggregation. Despite my efforts, the subtotals are not displaying as expected. Background In my 'Table_2', there's a column named (0-1) that I specifically want to display as it is, without any aggregation, in a matrix visual. To ensure that Power BI displays the exact values from this column without summing them up or applying any other aggregate functions, I've created a measure with the following DAX formula: 0-1 Measure= VAR SelectedValue = SELECTEDVALUE('Table_2'[(0-1)]) RETURN IF(ISBLANK(SelectedValue), 0, SelectedValue) Sample Input Data Below is a sample of the data from 'Table_2': Names (0-1) AA 200 BB 300 CC 400 DE 300 Matrix Visual Configuration - Rows: Names - Values: The measure based on the (0-1) column Current Output with Issue The matrix correctly displays the individual (0-1) values for each ID, but the subtotal is incorrectly showing as 0. Matrix Visual: Names (0-1) AA 200 BB 300 CC 400 DE 300 Subtotal 0 Desired Output I need the subtotal to accurately sum the individual (0-1) values: Names (0-1) AA 200 BB 300 CC 400 DE 300 Subtotal 1200 The Challenge How can I modify my measure or adjust the matrix settings to ensure that the subtotals correctly calculate the sum of the (0-1) values at each hierarchy level? I would greatly appreciate any advice or solutions that could help me resolve this issue with the subtotals. Thank you for your time and assistance! Warm regards, SBC
Solved
SBC
2 years ago Place DAX Commands and Tips
2.6KViews
0likes
7Comments
TABLE VISUAL - NOT SHOWING CORRECT VALUE
Hello Everyone, I'm a newbiew working on a BI report, I want to show/display the header (Questions) as rows. Then categorize it now by 2 columns which is Manufacturer Rate & Supplier Rate. The concern is I use a filter to sort/display the result below by using the respondent category and supplier name. If this question was raise before and resolved please help me redirect to that same question. Appriciate any support provided. Sample I will use the following filter; Respondent = Manufacturer, Supplier = ABC Enterprise. The expected output should be like, on Question | Manufacturer Rate | Supplier Rate PPM's L12M | 6 | 7 Data Source Below: Respondent Category PPMs L12M: PPMs L12M Remarks: PPMs L3M: PPMs L3M Remarks: Sub-Tier Supplier Management: Sub-Tier Supplier Management Remarks: Manufacturer 6 TEST ONLY 9 TEST ONLY 1 TEST ONLY Supplier 7 TEST ONLY 7 TEST ONLY 2 TEST ONLY EXPECTED OUPUT: Questions: Manufacturer Rate Supplier Rate PPMs L12M 6 7 PPMs L3M 9 7 Sub Tier 7 2
Solved
Cholo
2 years ago Place Custom Visuals Development Discussion
1.8KViews
0likes
4Comments
Rolling Sum for a subset of data
Hi Everybody, I am struggling with a DAX Formula: Calculation works for the overall time range, as you can see in the table, but it doesn't work for specified time range (15th to 23rd ). The rolling sum for this time range should start with 0 instead of 1 This is my DAX Formula: Cumulative Rolling Sum Value_B = CALCULATE( SUM('Sheet1'[Value_B]), FILTER( ALL('Sheet1'), 'Sheet1'[Date] <= MAX('Sheet1'[Date]) ) ) Any ideas what I am doing wrong ?
MXSven
2 years ago Place DAX Commands and Tips
472Views
0likes
2Comments
Export all filtered data or All visuals from Power BI Report Server (On-Permise) to single Excel
Hi expert, I am deploying pbi dashboard to report server but at the web end I want to export all filtered data from all visuals in a single click not one by one visuals to single excel file. How can I do it. Is there any tool or solution. Please guide on the perspective of On-Permise Server. I checkout power automate but it not suits my requirements due to on-permise constraint. Regard Naveed
Solved
voidbyname
2 years ago Place Report Server
3KViews
0likes
5Comments
Dynamic table
Hello, I'm trying to create a dynamic table in Power BI. I'll give the context: I have an Excel file with several data tables. I have a Power BI dashboard with a visual Filter and each filter corresponds to a table in the Excel file. I would like the table to change depending on the filter selected, taking the right data from the right table in the Excel file (depending on the filter selected on the visual, the table should take the right table and display the right data on the report). My idea: I thought of creating a calculated table with a formula that would say: "If on visual X, filter Y is selected then my table is T1 otherwise my table is T2." Something like that. In my idea, I need to specify exactly which visual it is, either with the exact name of the visual, or with a unique id. If this is not possible, can you suggest an alternative solution? Thanks in advance
AMZ
2 years ago Place DAX Commands and Tips
535Views
0likes
1Comment
Calculate variance dynamically between two dates even part is replaced
Hi Team, we have a requirment to calculate variance between two dates (slicer to select date1 and date2). Bridge list contains Old index and New index -- which means old index is a Index on Date1 and New Index is replaced Name. even though they looks different but they are same. we should do variance between INDIA|PUNJAB|A9867|3 INDIA|PUNJAB|A9898|3 Bridge List Old Index New Index INDIA|PUNJAB|A9867|3 INDIA|PUNJAB|A9898|3 SOURCE COUNTRY STATE Name ID Colour Value DATE INDEX INDIA DELHI Y1864 1 GREEN 60 27-Jan-23 INDIA|DELHI|Y1864|1 INDIA GOA Z2345 2 BLUE 70 27-Jan-23 INDIA|GOA|Z2345|2 INDIA PUNJAB A9867 3 GREEN -190 27-Jan-23 INDIA|PUNJAB|A9867|3 INDIA DELHI Y1864 1 GREEN 80 31-Jan-23 INDIA|DELHI|Y1864|1 INDIA GOA Z2345 2 BLUE 70 31-Jan-23 INDIA|GOA|Z2345|2 INDIA PUNJAB A9867 3 GREEN -150 31-Jan-23 INDIA|PUNJAB|A9867|3 INDIA PUNJAB C1234 4 GREEN -100 31-Jan-23 INDIA|PUNJAB|C1234|4 INDIA DELHI Y1864 1 GREEN 60 2-Feb-23 INDIA|DELHI|Y1864|1 INDIA GOA Z2345 2 BLUE 70 2-Feb-23 INDIA|GOA|Z2345|2 INDIA PUNJAB A9898 3 GREEN -200 2-Feb-23 INDIA|PUNJAB|A9898|3 INDIA PUNJAB C1234 4 GREEN -100 2-Feb-23 INDIA|PUNJAB|C1234|4 OUTPUT1 Select slicer1 & slcier 2 (Date1 27-Jan-2023 & Date2 31-Jan-2023) INDEX ON DATE1 INDEX ON DATE2 COLOUR ON DATE1 COLOUR ON DATE2 VALUE ON DATE1 VALUE ON DATE2 DELTA INDIA|DELHI|Y1864|1 INDIA|DELHI|Y1864|1 GREEN GREEN 60 80 20 INDIA|GOA|Z2345|2 INDIA|GOA|Z2345|2 BLUE BLUE 70 70 0 INDIA|PUNJAB|A9867|3 INDIA|PUNJAB|A9867|3 GREEN GREEN -190 -150 40 INDIA|PUNJAB|C1234|4 GREEN -100 -100 OUTPUT2 Select slicer1 & slcier 2 (Date1 = 31-Jan-2023 & Date2 2-Feb-2023) On 2-Feb-23 INDIA|PUNJAB|A9867|3 is replaced as INDIA|PUNJAB|A9898|3 so PBI should identify the replacement by referign brdigelist tablew INDEX ON DATE1 INDEX ON DATE2 COLOUR ON DATE1 COLOUR ON DATE2 VALUE ON DATE1 VALUE ON DATE2 DELTA INDIA|DELHI|Y1864|1 INDIA|DELHI|Y1864|1 GREEN GREEN 60 60 0 INDIA|GOA|Z2345|2 INDIA|GOA|Z2345|2 BLUE BLUE 70 70 0 INDIA|PUNJAB|A9867|3 INDIA|PUNJAB|A9898|3 GREEN GREEN -190 -200 -10 INDIA|PUNJAB|C1234|4 GREEN -100 -100 Regards, Reddy.
tangutoori
2 years ago Place Report Server
698Views
0likes
1Comment
TotalYTD or DatesYTD based on SLicer filter
Hello everyone, I have a totalytd measure, and creating a bar chart based on date and total sales. Also I have a slicer which contains Month Name. If I filter for March month, the bar chart will show data only for March month but my requirement should be like the visual should display till March month (Jan, Feb and Mar). Likewise if I select June month in the slicer, the bar chart should display data from January to June. How to achieve this, please advice.
Arioli_Chezhian
2 years ago Place DAX Commands and Tips
437Views
0likes
1Comment