User Profile
PavanLalwani
Resolver II
Joined 4 years ago
User Widgets
Contributions
Re: How to show comment of only all category in matrix visual
To show the comment only for the "cat-All" category in your Matrix visual without splitting into additional rows with blanks, you can use a calculated measure instead of a calculated column. Using a measure will give you control over the display in the Matrix visual, as measures dynamically adjust based on the visual context. Here's how to set it up: ### Step 1: Create a Measure for the Comment Create a measure that returns the comment only for rows where `Category = "cat-All"` and leaves other categories blank in the Matrix visual: ```DAX Display_Comment = IF( SELECTEDVALUE(Table[Category]) = "cat-All", MAX(Table[comments]), BLANK() ) ``` ### Explanation - `SELECTEDVALUE(Table[Category])` checks the current category in the context of the Matrix visual. - If the category is "cat-All," it returns the comment using `MAX(Table[comments])` (or any other aggregation that makes sense for your data). - If the category is not "cat-All," it returns `BLANK()`, so no additional rows with blank comments appear. ### Step 2: Add the Measure to the Matrix Visual 1. Place `Display_Comment` in the **Values** section of your Matrix visual instead of the original `comments` column. 2. Ensure that only the "cat-All" category has comments displayed. ### Step 3: Remove Blank Rows in the Matrix Visual In Power BI, you can remove blank rows by adjusting the **Show items with no data** setting: 1. Go to **Format > Row headers**. 2. Toggle **Show items with no data** off. This hides any rows where the measure (`Display_Comment`) returns `BLANK()`. This approach will display comments only for "cat-All" and eliminate the blank rows from the Matrix visual. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!971Views0likes0CommentsRe: Project ID wise Status (In Query Editor)
In Power Query, you can achieve this prioritized filtering by creating custom steps that apply conditional logic to set the Record Status for each Project ID based on priority. Here’s how to do it step-by-step in Power Query Editor: Step-by-Step Solution Load the Data into Power Query: Load your dataset into Power Query by selecting the table and going to Transform Data. Group Rows by Project ID and Identify the Priority Status: Go to Home > Group By. Group by Project ID, and add a new aggregation column called PriorityStatus using the All Rows option. This will create a nested table with all rows for each Project ID. Add a Custom Column to Determine the Priority Status for Each Project ID: Click on Add Column > Custom Column and enter the following code to determine the highest priority status for each project based on your rules: M Copy code = Table.AddColumn(#"Grouped Rows", "PriorityStatus", each if List.ContainsAny(Table.Column([All Rows], "Record Status"), {"Off Track"}) then "Off Track" else if List.ContainsAny(Table.Column([All Rows], "Record Status"), {"On Track"}) then "On Track" else "Not Applicable" ) This PriorityStatus column checks if "Off Track" exists in any row for the Project ID, if not, checks for "On Track", and defaults to "Not Applicable" if neither is present. Expand the Table Back to Original Format: Now, expand the All Rows column to return the table back to its original form. Click on the expand icon next to the All Rows column, uncheck any unwanted columns, and expand Product Name and Record Status columns. Add Conditional Column to Nullify Non-Priority Rows: Go to Add Column > Conditional Column to create a new column Filtered Status where only rows with the priority status are retained. Use this formula in the conditional column: If [Record Status] equals [PriorityStatus], then [Record Status]. Otherwise, null. Remove the Original Record Status Column: Go to Home > Remove Columns and delete the original Record Status column, keeping only the Filtered Status column. Rename Filtered Status to Record Status: Rename the Filtered Status column back to Record Status to match your expected output. Finalize and Load Data: Click Close & Load to load your transformed data back into Power BI with the correct priority status. Summary of Transformation This approach groups by Project ID, calculates the highest-priority Record Status for each project, expands the data back to its original form, and uses conditional logic to set all lower-priority rows to null. This will give you the expected output where only the highest-priority status is retained per Project ID. This should help you achieve the prioritized filtering in Power Query! If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!1.9KViews1like1CommentRe: Weighting with Goals and Total Score
To achieve a weighted supplier evaluation score, you'll want to calculate each question's score based on the goal it belongs to, apply the goal-specific weights, and then calculate a weighted average to get a total score for each supplier on a 1-4 scale. Here's a step-by-step approach to structure the solution in Power BI: 1. Set Up Your Data Structure Ensure your data model has these elements: A Questions Table with columns for Question ID, Goal, and Weight. Each question should be assigned to a goal, and each goal should have a specific weight (e.g., Goal 1 = 30%, Goal 2 = 50%, Goal 3 = 20%). A Ratings Table with columns for Question ID, Supplier ID, and Rating (on a scale from 1 to 4). 2. Calculate Weighted Scores for Each Question For each question, you'll calculate a weighted score that considers the goal's importance. Use a Calculated Column in Power BI to do this. Assuming you have a relationship between your Questions Table and Ratings Table based on Question ID, create the following calculated column in the Ratings Table: DAX Copy code WeightedScore = RELATED(Questions[Weight]) * Ratings[Rating] This formula multiplies the Rating by the Weight of the corresponding goal for that question. 3. Calculate the Total Weighted Score for Each Supplier To calculate a total weighted score for each supplier, create a Measure in the Ratings Table: DAX Copy code TotalWeightedScore = SUMX( Ratings, Ratings[WeightedScore] ) This measure will sum up all the weighted scores for each supplier, giving you a total score that accounts for the weight of each goal. 4. Normalize the Total Score to a 1-4 Scale Since you want the final score to be on a 1-4 scale, calculate a Normalized Score. Create another Measure for this, which divides the TotalWeightedScore by the sum of the weights to ensure it fits the 1-4 scale: DAX Copy code NormalizedScore = DIVIDE( [TotalWeightedScore], SUM(Questions[Weight]) ) This NormalizedScore measure will provide the final weighted score for each supplier on a 1-4 scale, accounting for the goal weights. 5. Display the Final Results Now, you can display the NormalizedScore measure in your report visualizations to show the weighted score per supplier, which should reflect the weighted impact of each goal. Additional Tips If you need to adjust the weightings, you can do so in the Questions Table without changing the underlying calculations. Ensure that all your weights add up to 1 (or 100%) to keep the final score on a comparable scale. This setup should give you a flexible and scalable model that allows each goal’s weight to influence the total supplier score effectively. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!2.6KViews0likes2CommentsRe: Reduce loading wtih a huge dataflow
Handling such a large dataset in Power BI can be challenging, but there are ways to optimize your dataflow to reduce loading time and memory usage. Here are solutions to focus on loading only the required 2 million rows, either by filtering before data import or managing the data loading process more efficiently. 1. Filter Data in the Dataflow Itself (Preferred) If possible, filter the data in the dataflow before it even reaches Power BI. This way, only the necessary rows are loaded into Power BI, significantly reducing memory load. Modify the Dataflow Query: If you have control over the dataflow, add filtering steps in the dataflow query to limit the data to the required 2 million rows. If using a specific column or condition to filter (like date range or category), add these as steps in the dataflow before Power BI imports the data. Benefits: This is the most efficient method, as it reduces the dataset size before Power BI interacts with it. 2. Use DirectQuery Instead of Import Mode Switch to DirectQuery Mode: If filtering in the dataflow is not possible, consider using DirectQuery mode instead of Import mode. With DirectQuery, Power BI doesn’t load the entire dataset; instead, it queries the database directly for the 2 million rows you need, based on your filters. Limitations: Be aware that DirectQuery can impact report performance depending on your database’s response time and the complexity of your queries. 3. Incremental Refresh with Parameters (if Import Mode is Required) Use Incremental Refresh: Set up Incremental Refresh on your data to load only a subset of data based on a defined filter, such as date range or partitioning key. This way, Power BI only refreshes data that has changed or falls within the specified parameter range, instead of reloading all 185 million rows. How to Set It Up: In Power BI, go to Modeling > Manage Parameters and create parameters for filtering (e.g., a date range). Set up Incremental Refresh by right-clicking your table, selecting Incremental Refresh, and configuring it with the parameters you've defined. Once set up, only the relevant partition (e.g., the 2 million rows) will load and refresh, significantly reducing data load time. 4. Power Query Filtering with Table Partitions If you cannot modify the dataflow or use DirectQuery, you can try partitioning the data in Power Query to limit the rows loaded. Add Filtering Steps in Power Query: In Power Query, add steps early in the query to filter out rows. For instance, use Text/Date/Number filters to keep only the relevant 2 million rows. Enable Query Folding: To improve efficiency, make sure your filtering step happens as early as possible and that it allows query folding (where the source system does the filtering instead of Power Query). This way, only the required data is transferred to Power BI. Partition Loading: If possible, split your query into multiple smaller queries with different filtering criteria, and load them as separate tables or partitions. This can improve manageability and sometimes speed up data handling. 5. Use Aggregated Tables with Composite Models (if Import and Aggregations are Required) Create Aggregated Tables: Load only aggregated data instead of detailed rows. If detailed data is only occasionally needed, create summary tables with relevant aggregations (like totals, counts, or averages), and use composite models to link them with the main table. Benefits: This reduces the volume of data Power BI needs to process and allows it to handle the larger dataset only when needed. Each of these methods has trade-offs, but filtering directly in the dataflow or using DirectQuery will generally be the most efficient. If the underlying data source is too slow for DirectQuery, consider Incremental Refresh to avoid loading the entire dataset repeatedly. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!1.8KViews1like1CommentRe: Changed datatype to date in power query, but return as text in model view
The issue you’re facing is likely due to inconsistent date formats in the data and Power BI's handling of date formats when importing from Power Query to the data model. Here’s a step-by-step approach to resolve this: 1. Set Date Format in Power Query In Power Query, ensure all dates are consistently formatted before loading them into the model. Confirm that your "Date/Time" column is set to a single, consistent format. If necessary, use the Transform > Format options in Power Query to convert all dates to the desired format (e.g., "MM/DD/YYYY" or "DD/MM/YYYY"). 2. Re-Check Data Type in Power Query After ensuring consistent formatting, explicitly set the column to Date/Time in Power Query. Close & Apply your changes to reload the data back to Power BI with the correct data type. 3. Check Regional Settings in Power BI Desktop Power BI uses regional settings that may affect date interpretation. If dates are showing inconsistently (some as DD/MM and others as MM/DD), this could be due to regional settings. To adjust this, go to File > Options and settings > Options > Regional settings in Power BI Desktop and set it to match the date format you need. This will help Power BI interpret dates in a consistent manner. 4. Convert to Date/Time in Model View In the Model View, reselect the "LatestEvent.timestamp" column and set its Data type to Date/Time. If you still see inconsistent formats, try creating a calculated column that explicitly formats the date: DAX Copy code ConvertedDate = DATE(YEAR('YourTable'[LatestEvent.timestamp]), MONTH('YourTable'[LatestEvent.timestamp]), DAY('YourTable'[LatestEvent.timestamp])) This DAX formula extracts the year, month, and day to ensure a consistent format, bypassing any underlying text formatting issues. By following these steps, Power BI should display the dates consistently in your model. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!2.3KViews0likes0CommentsRe: Meantime to Close Column
To calculate the Mean Time to Close (MTTC) in Power BI, you can create a calculated column that calculates the time difference between CreatedDateTime and ResolvedDateTime for each incident. Here’s how you can set it up: Step 1: Create a New Calculated Column In your Power BI report, go to the table where your incident data is stored. Click on Modeling in the toolbar, then select New Column. Enter the following DAX formula: DAX Copy code MTTC_Hours = IF( ISBLANK([ResolvedDateTime]), BLANK(), DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) ) Explanation: DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) calculates the difference in hours between the creation and resolution times. The IF condition checks if the ResolvedDateTime is blank (meaning the incident is still open) and, if so, returns a blank value instead of a time difference. Step 2: Customize Time Units if Needed If you’d prefer the time difference in minutes or days, change the last argument in DATEDIFF: For minutes: replace HOUR with MINUTE For days: replace HOUR with DAY Step 3: Calculate the Average Mean Time to Close (Optional) If you want an overall average MTTC, create a Measure instead: Go to Modeling and select New Measure. Enter this formula: DAX Copy code Average_MTTC_Hours = AVERAGE('YourTable'[MTTC_Hours]) Replace 'YourTable' with the name of your table. This measure will give you the average time to close across all incidents that have been resolved. With this setup, you’ll have both a per-incident MTTC and an overall average MTTC for closed incidents. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!656Views1like0CommentsRe: Drill Through of power bi
In Power BI, creating a dynamic drill-through experience that directs users to different pages based on their selection can be a bit complex, as Power BI doesn’t natively support conditional drill-through navigation to multiple pages. However, you can achieve a similar result by following these steps: Step 1: Set Up Filtered Pages with Drill-through Capability Create Drill-through Pages: - For each page (Page 2 for Category-wise sales and Page 3 for Subcategory-wise sales), add a Drill-through Filter based on either Category or Region, depending on how you want to structure the navigation. Enable Drill-through Filter: - Go to each drill-through page and add the relevant field (Category or Region) to the Drill-through Filters pane. This will enable drill-through navigation from other pages to this page based on that field. Add a Back Button (optional but recommended): - To improve user experience, add a Back button to each drill-through page so users can easily return to the main page. Step 2: Use Conditional Navigation with Buttons (Workaround for Multiple Drill-through Destinations) Since Power BI doesn’t support direct conditional drill-through for multiple pages, you can use buttons with Bookmarks and Navigation Actions to simulate this: Create Buttons for Navigation: - On Page 1 (the Region-wise Sales page), create individual buttons for each region (e.g., East, West, etc.). These buttons will act as a way for users to navigate to specific pages based on their selection. Assign Actions to Buttons: - For each button, set up an Action: - Select the button, go to Format → Action. - Enable Action, set Type to Page Navigation, and select the target page (e.g., Page 2 for Category-wise Sales, Page 3 for Subcategory-wise Sales) based on the region or category you want. Use Bookmarks for Enhanced Interactivity (Optional): - If your report is complex and requires additional interactivity, consider using Bookmarks in combination with buttons. This allows you to save specific views or filtered states of the page, which can then be linked to buttons for a customized navigation experience. Step 3: Test the Navigation - On Page 1, test the navigation by clicking each button and verifying it takes you to the intended page based on the region or category. - Check that drill-through filters applied to Pages 2 and 3 show the correct filtered data based on the navigation path. Limitations and Alternative Suggestions Currently, Power BI does not natively support conditional drill-through to different pages based solely on selection criteria. While buttons and page navigation provide a reasonable workaround, another solution could be using Power BI Report Server or Power BI Embedded with custom development if this is a critical need. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!1.1KViews0likes0CommentsRe: Help with a % of Total
To display the correct "% of Total" rent for the top 10 tenants in your Power BI table (where each tenant’s percentage is calculated against the total rent of the selected portfolio and month, not just the top 10), you can use a DAX measure that calculates the percentage of the overall total, even with slicers applied. Here’s how: Step 1: Calculate the Total Rent for the Selected Portfolio and Month This measure calculates the total rent for the selected portfolio and month, ignoring the tenant filter to get the overall total for that selection. ```DAX TotalRentForSelection = CALCULATE( SUM('Table'[Rent]), REMOVEFILTERS('Table'[Tenant]) ) ``` In this example: - `'Table'` represents the name of your table. - `REMOVEFILTERS('Table'[Tenant])` ignores any specific tenant filter applied by the top 10 filter context. This measure will always return the total rent amount for the selected month and portfolio, regardless of the tenants shown. Step 2: Create the % of Total Measure Now, create a second measure to calculate the percentage of the total for each tenant based on the overall rent from the selected portfolio and month. ```DAX % of Total Rent = DIVIDE( SUM('Table'[Rent]), [TotalRentForSelection], 0 ) ``` - This measure calculates each tenant’s rent as a percentage of the overall `TotalRentForSelection`. - `DIVIDE` handles division by zero gracefully if there is no rent in the selected month/portfolio. Step 3: Apply a Top N Filter in Your Visual In the visual, use the Top N filter to show only the top 10 tenants. The `% of Total Rent` measure should now display each tenant’s rent as a percentage of the total rent in the selected portfolio and month, rather than as a percentage of just the top 10 tenants. Optional: Add Visual-Level Filters for Slicers You can add slicers to select the month and portfolio, so the visual automatically adjusts to the selected criteria and displays the correct total percentage based on the overall rent. Summary Table Example With this setup, your table should look something like this for the selected portfolio (e.g., "X") and month (e.g., "June 2024"): | Month | Tenant | Portfolio | Rent | % of Total Rent | |-----------|--------|-----------|------|-----------------| | June 2024 | A | X | £100 | 10% | | June 2024 | B | X | £200 | 20% | | June 2024 | C | X | £250 | 25% | Now, the `% of Total Rent` column reflects the percentage based on the overall total rent for the selected portfolio and month, not just the top 10 tenants. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!1KViews1like1CommentRe: Different results on two cards wuth the same DAX and filters
If you’re seeing different results (or blanks) in Power BI visuals when using the same DAX calculations and filters, here are some steps to investigate and potential solutions to address the issue. Often, the blank results may stem from relationships, context, or model complexity, especially if you have a complex model with dependencies across several dimensions. 1. Check Relationships in the Model - Confirm Active Relationships: Ensure that the relationship between `'P6 Actual Hours Over Time'` and other relevant tables is active and set correctly. If your visual is set to use a field from another table, and the relationship isn’t active or is missing, you might see unexpected blanks. - Relationship Direction: For complex data models, especially when connecting by multiple keys (e.g., Date, Resource, Area), ensure the cross-filter direction is appropriate (single or both). Using both can help in specific cases but can also introduce complexity or performance issues. 2. Filter Context Troubleshooting - Confirm Filter Contexts for Each Card: Even though it seems the filters are the same, subtle differences in context can occur with complex models. Try using a `CALCULATE` function to explicitly define the filter context. For example, modify your measure to ensure context: ```DAX EV(P6) = CALCULATE(SUM('P6 Actual Hours Over Time'[Hours])) ``` - Test in Table Format: Create a table visual with `'P6 Actual Hours Over Time'[Hours]` to see if the expected rows and values appear. This can help confirm whether filters are indeed being applied or if a relationship is blocking the expected values from displaying. 3. Check for Hidden Filters or Data Integrity Issues - Hidden Filters in Report View: If you’re working with multiple visuals, there may be hidden slicers or page-level filters impacting only certain visuals. Go to View > Filters Pane to review all applied filters, including any page or report-level filters. - Null or Blank Values in Relationships: If there are any `NULL` or blank values in the connecting columns (e.g., Date, Resource, Area), this can break or cause inconsistencies in relationship propagation. Check your columns for these values and handle them in Power Query if necessary. 4. Simplify Dependencies in the Model - Since you mentioned logical dependencies are numerous, simplifying dependencies or using aggregated tables for critical measures could help. Too many dependencies can occasionally lead to unexpected context errors or performance issues, especially with many-to-one relationships. 5. Check Model Performance - Performance Analyzer: Use Power BI’s Performance Analyzer tool (in the View tab) to see how long each visual takes to load. This can indicate if there are slow-running queries or complex model dependencies impacting certain cards. - Optimize DAX Calculations: Although `SUM` is a simple function, if you use more complex calculations later, try using variables in your measures to limit the amount of data Power BI needs to calculate each time. 6. Validate in Power Query - Refresh Data and Preview: In Power Query, check if any transformations might have affected data integrity and refresh all previews to ensure the latest data loads properly. Sometimes, unrefreshed tables or connection issues can impact visuals unexpectedly in Power BI. Additional Debugging Tips If none of the above steps help, try creating a new, isolated measure card with your `EV(P6)` formula outside of any context. This can help rule out whether the issue is with the data model, filters, or the DAX measure itself. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!1KViews1like2CommentsRe: selected measure formating and/or default measure
To apply default formatting for a measure in Power BI when no measure is selected, you can set up conditional formatting that applies your specific formatting to the “Sales USD” measure. Here’s how to handle it so the graph displays both the values and formatting dynamically, even with default settings: Step 1: Define the Measures with Specific Formatting Make sure your measures are individually formatted the way you want them to display. For instance, set “Sales USD” to display as `,0,;(,0,);-`. Step 2: Create a Switch-Driven Dynamic Measure Use a `SWITCH` function to dynamically display the selected measure or default to "Sales USD" when nothing is selected. This will give you control over the measure displayed when a selection isn’t made. Go to Modeling > New Measure and create a new measure (e.g., `DynamicMeasure`) using this DAX formula: ```DAX DynamicMeasure = SWITCH( TRUE(), ISSELECTEDMEASURE([Measure1]), [Measure1], ISSELECTEDMEASURE([Measure2]), [Measure2], ISSELECTEDMEASURE([Measure3]), [Measure3], [Sales USD] ) ``` This formula will display any selected measure dynamically, defaulting to `[Sales USD]` if nothing is selected. Step 3: Apply Custom Formatting for the Default Measure To apply the specific formatting `,0,;(,0,);-` when “Sales USD” is the default, we’ll create a separate formatted measure or use DAX formatting. New Formatted Measure: If needed, create another measure specifically for formatting: ```DAX SalesUSDFormatted = FORMAT([Sales USD], ",0,;(,0,);-") ``` Conditional Formatting in the Visual: For the chart visual, set up a conditional formatting rule to apply the custom format if the “Sales USD” measure is used. Go to the Format section of your chart, choose Data Labels, and use Conditional Formatting with your specified format settings. Test the Setup: Ensure that when a measure is selected, it displays accordingly, and if no measure is selected, "Sales USD" appears with the desired formatting. Optional: Use Field Parameters for Enhanced Switching Field Parameters, a Power BI feature, can simplify dynamic measure switching. You can set up parameters that allow users to select which measure to display in a chart, and the default measure will fall back to "Sales USD." By setting up dynamic switching with conditional formatting, your chart should always show the selected measure with its formatting or revert to "Sales USD" with the default format if nothing is selected. If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!916Views0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.