User Profile
adityaupowerbi
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Dataset refresh timing improvement
Hi All, I am facing issues with dataset refresh failure in Power BI. Here the issue and my approach in a nutshell: 1) We had around 190 column and 195 million records in our dataset, this was a csv earlier and then we moved to a sql server table. 2) There are only 5-6 dimension tables. Other than that, there were 180 plus metric columns for current year, last year and year before last year. 3) We transformed the table into such a way that now columns are pivoted to year type and metric. So we have now around 40 columns but records are increased to 3 times, around 560 million records. So we managed to reduce columns by 1/3rd but record count increased by 3 times. We thought narrow table perform better than wide table. 4) But now neither we are able to query table in SQL server, nor we are able to refresh it completely in Power BI. Even for setting up incremental refresh we need to refresh it one time fully. This is not happening. Appreciate any suggestions, what can be done. As of now, 8 descriptive columns and 31 measure columns are present.Syncing slicers between reports
Hi All, I have around 10+ dashboards in workspace. All the reports are embedded in a web portal. The URLs for each of these dashboards are pasted on a button visual as hyperlink. Basically all the reports can be navigated using buttons. The requirement is all the reports have a Client slicer. So if a user select something in Client slicer in Report 1 and click on the hyperlink button for Report 2, the Report 2 should also show same selection in Client slicer. I tried using query string parameter, but saw two issues with that. 1) It produces static URL like some hardcoded client value. It cannot capture what the user selects 2) It does not change the embed URL which is used in the portal. Appreciate help here.Subtotal label resetting to title "Total" in matrix visual when exporting to excel
I dont know if this is a bug or limitation but even when I change the sub total label in row subtotals section of matrix to Average. It resets to Total when the matrix data is exported with summarized layout in excel. When I export the matrix data even though it is showing Average in Power BI service and desktop it show total in excel: Please help.Re: Need same value in all the rows of a column for calculation and percentages
Hi, Here is the sample data and requirement in more detail: Raw data MeasureType Status Status_new goal_num goal_den actual_num actual_den M1 Admited Admitted 100 200 70 10 M1 Goal Enrolled Goal 200 400 0 0 M1 Confirmed Confirmed 0 0 180 20 M2 Admited Admitted 120 220 140 70 M2 Goal Enrolled Goal 220 420 0 0 M2 Confirmed Confirmed 0 0 200 50 Measures actual value = DIVIDE(SUM(actual_num),SUM(actual_den),0) actual = calculate([actual value],table[Status_new]="Confirmed") goal = CALCULATE(DIVIDE(SUM(table[goal_num]),SUM(table[goal_den]),0),table[status_new]="Enrolled Goal") % Goal shoule be actual/goal and comes only when the status is Confirmed and blank for other status Output Admitted Goal Confirmed Measure Type actual goal %goal actual goal %goal actual goal %goal M1 BLANK BLANK BLANK BLANK 0.5 BLANK 7 BLANK 14 M2 BLANK BLANK BLANK BLANK 0.523809524 BLANK 2 BLANK 3.818182Re: Need same value in all the rows of a column for calculation and percentages
Jihwan_Kim Thanks for the reply. But in my case the column 1 and column 2, which are the denominator and numerator respectively , they are measures. Col 1 = CALCULATE(SUM(measure1), status = "B" && column 3 = "H") Col2 = CALCULATE(SUM(measure2), status = "C" && column 3 = "H") When I apply is filter on col1 , i still get blank for all the rows except where the value is B. measure 1 and measure 2 are simple numeric columns like sales.Need same value in all the rows of a column for calculation and percentages
Hi All, I have data like below: Status Column1 Column2 A 10 0 B 20 0 C 30 40 Output Status B value %C/B A 20 0 B 20 0 C 20 40/20 = 200% I am unable to keep the value 20 in all the rows here. Also for the percentages I need to divide value for status C in column 2 by value for status B in column 1. Which means 0 for other rows but value only for row where we have value for C in column2 Jihwan_Kim Appreciate your help.
