User Profile
srlabhe
Super User
Joined 1 year ago
User Widgets
Contributions
Re: disaster recovery and version control in power bi
Power BI disaster recovery and version control are best managed by storing .pbix files in SharePoint/OneDrive for automated version history, using Power BI Desktop projects (.pbip) with Git for advanced tracking, and employing Deployment Pipelines or third-party tools like Power BI Sentinel for automated backups and recovery. Version Control Strategies Because .pbix files are binary, traditional code versioning (like Git) is difficult. OneDrive/SharePoint (Simple): Save files directly to OneDrive for Business or SharePoint. They automatically maintain version history, allowing you to restore previous versions of .pbix files. Git/Azure DevOps (Advanced): Use the Power BI Project (.pbip) format. This saves report metadata in plain text (TMDL), which allows Git to track changes, compare versions, and revert changes. Deployment Pipelines (Premium): Use deployment pipelines to manage content across Development, Test, and Production workspaces. This allows you to roll back to a previous, stable version in a higher environment if needed. Disaster Recovery (DR) Techniques Regular Backups (Manual/Automated): Manually export reports (.pbix) regularly, or automate this using the Power BI REST API to create scheduled backups. Third-Party Tools (e.g., Power BI Sentinel): Use specialized tools that provide automated daily backups, change monitoring, and quick recovery features. Regional Failover (Service Level): Microsoft manages data redundancy between regional pairs to handle major outages. Self-Service Disaster Recovery (Power Platform Admin Center): Enable this to replicate data between primary and secondary locations for high-priority workspaces. Best Practices Use "Thin" Reports: Separate data sets (.pbix with data) from reports (.pbix with visuals only). This makes, recovery faster because only the report layer needs restoring. Define RTO/RPO: Establish Recovery Time Objectives (how quickly to recover) and Recovery Point Objectives (how much data can be lost). Regular Drills: Periodically test restoring from backups to ensure they work.705Views1like2CommentsRe: Calculate Rolling Sickness Rate From Cumulative DataSet
To calculate a rolling 12-month sickness percentage with cumulative, duplicated employee data, you must calculate Available FTE on a month-by-month basis, then sum these values over a 12-month rolling period. Using Pivot Tables or Power Query is best, ensuring you sum FTE by Month/Employee ID before calculating the rolling total. Step-by-Step Approach: 1. Clean and Organize Data: Ensure all tables have a consistent "Month" identifier (e.g., end of month date). 2. Calculate Monthly Available FTE (Solving Duplication): Do not just sum the FTE column. Because employees are duplicated with different FTEs per month, create a Pivot Table. Rows: Month, Employee ID Values: Max of FTE (or Average of FTE if they have multiple entries in one month). Alternative (Power Query): Group by Month and Employee ID, taking the maximum FTE to ensure one accurate entry per person per month. 3. Sum Total Available FTE (12-Month Roll): Use a SUMIFS formula or a Pivot Table to sum the Monthly FTE figures calculated above for the 12-month period preceding the report date. 4. Calculate Sickness Days: Aggregate total sickness days from the Sickness Table for the same 12-month rolling period. 5. Calculate %: Note: If sickness is measured in days, convert FTE to days (e.g., FTE x 21.67 average working days per month). This ensures that if an employee changed from 0.5 FTE in January to 1 FTE in February, those changes are captured correctly by the specific month, rather than double-counted.549Views0likes1CommentRe: GL-Acount - Accscheduleline85 relationship
Relating G/L Account (Table 15) and Account Schedule Line (Table 85) in Power BI requires bridging because Table 85 holds formulas or ranges in the "Totaling" field (e.g., "1000..2000" or "Account1+Account2") rather than direct foreign keys to account numbers. Here is the recommended approach to build this data model: 1. Which Bridge Table to Use? There is no single native "bridge" table in Business Central that connects them directly in a 1:N fashion. The best approach is to create a Mapping Table in Power Query (using DAX or M-code) to parse the Totaling column from the Acc. Schedule Line and break it down into individual G/L account numbers. Option A (Best Performance): Use DAX in Power BI to create a calculated table that expands the Totaling field, or use Power Query to parse ranges/lists. Alternative: Use G/L Account Categories to generate the financial reports instead of raw Acc. Schedule Lines, as these are mapped to GL accounts by default. 2. Appropriate Columns for the Relationship Since the Totaling field can contain ranges (1000..2000) or specific accounts (1000|2000), a standard relationship won't work. You must normalize the data. The Mapping Strategy: Acc. Schedule Line (Table 85): Use Schedule Name and Line No. as a unique identifier for the row in the report. G/L Account (Table 15): Use No. as the identifier. Bridge Concept: A BridgeTable with [AccScheduleLineNo] and [GLAccountNo]. 3. Existing Table Linking G/L Accounts and Account Schedule Lines There is no standard, out-of-the-box, relational table that maps them directly, as the Acc. Schedule Line (Totaling field) acts as a flexible formula-based filter rather than a static link. However, the best source to generate this linkage is using the "Generate Financial Reports" (previously Account Schedule) functionality, which auto-generates the mapping. Evolution Business Systems Evolution Business Systems Recommended Power BI Data Model Structure DimGLAccount: Table 15 (G/L Account) DimAccScheduleLine: Table 85 (Acc. Schedule Line) FactGLTransactions: General Ledger Entries (Table 17) Bridge Table (Recommended): Create a Power Query table that reads Acc. Schedule Line [Totaling] and [Totaling Type]. If Type = "Posting Accounts", parse the Totaling field into a list of individual GL accounts. This creates a table of [AccScheduleLine_ID], [GLAccountNo]. Alternatively, use G/L Account Categories and Subcategories to generate your reports. They provide a native, built-in structure to map G/L accounts to financial report rows.551Views0likes0CommentsDax for calculating count of Login on Bar Chart
Hi PBI Gurus, I have below table which is sample data of what I actually have ID Area login DynamicMeasure 1 East 111ABC Assigned 1 East 123PQR Unassigned 2 East 111ABC Assigned 2 West 111ABC Assigned 3 West 111ABC Assigned 4 West 123PQR Unassigned 5 West 456ABC Unassigned In Above table the first column ID is driver of data. If we take off first column the DynamicMeasure shows blank. Also we cant use the measure in Graph's Legend. We have slicer on Area, DynamicMeasure (I build slicer on measure creating separate data table and using a flag to compare data table values and DynamicMeasure to select) Now, when I am trying to put it on Graph it fails ,I need to put a distinct count of logins for each Area where DynamicMeasure in legend in clustred bar chart. Any suggestion ofr DAX creation please ?Solved761Views0likes5CommentsRe: Power BI data modeling question
To avoid bidirectional filtering, performance issues, and ambiguity, the recommended best practice is to structure your model into a proper star schema by creating a dedicated Dimension Table for shared keys (e.g., a DimOrder table) or ensuring a central bridge table connects to both fact tables. Do not create slicers directly from fact tables (Returns). Recommended Solutions: Create a DimOrder Table (Best Practice): Create a new table DimOrder consisting of distinct Order IDs from both the Orders and Returns tables. Create a 1-to-many single-direction relationship from DimOrder to Orders (on OrderID). Create a 1-to-many single-direction relationship from DimOrder to Returns (on OrderID). Use the Region from People (connected to Orders) and Order ID from DimOrder in your slicers. DAX TREATAS (Alternative): If rearranging the model isn't possible, create a DimOrder table and use DAX to map the relationship. dax FilteredOrders = CALCULATE( [Total Orders], TREATAS(VALUES('DimOrder'[OrderID]), 'Orders'[OrderID]) ) CROSSFILTER in Measures: To make a slicer on Returns affect Orders, use CROSSFILTER in a specific measure to change the filter direction only for that calculation, maintaining overall performance.993Views1like0CommentsRe: Show the last field according to the last date in a table
To show only the latest delay event per project in a Power BI table visual, create a measure to identify the maximum date per project, then use that to filter the cause. Use ALLEXCEPT to fix the context to the Project ID. DAX Measures: Latest Delay Date: dax Latest Delay Date = CALCULATE(MAX('YourTableName'[Date of Delay]), ALLEXCEPT('YourTableName', 'YourTableName'[Project ID])) Latest Delay Cause (Use this in Table Visual): dax Latest Delay Cause = VAR LastDate = [Latest Delay Date] RETURN CALCULATE( MAX('YourTableName'[Delay Cause]), FILTER( ALLEXCEPT('YourTableName', 'YourTableName'[Project ID]), 'YourTableName'[Date of Delay] = LastDate ) ) Steps to Configure: Add Project ID from your table to the visual. Add the [Latest Delay Date] measure. Add the [Latest Delay Cause] measure.441Views0likes0CommentsRe: Filter Max Date Range with Single Select Slicer
To create a report that limits all data points to the maximum date selected in a slicer, you need to use a disconnected Date table for the slicer and a DAX measure to filter the report visuals. Step-by-Step Guide Create a disconnected Date table for the Slicer Go to the Modeling tab and select New Table. Use DAX to create a new table for your slicer. This table should contain all possible dates you want to select. It should be independent (disconnected) from your main data table(s) to function correctly with the filtering measure. DAX Slicer Dates = VALUES('YourMainDataTable'[Date Column]) Make sure the relationship between this new Slicer Dates table and your main data table is inactive or non-existent. Create a Measure to Capture the Selected Date In your main data table, create a new measure to capture the maximum selected date from your new Slicer Dates table. DAX Max Selected Date = MAX('Slicer Dates'[Date Column]) Create a Filtering Measure Create a second measure to use in your visuals' filter panes. This measure compares the date in the current visual's context with the Max Selected Date from the slicer. DAX Filter Measure = VAR MaxDate = [Max Selected Date] VAR CurrentDate = MAX('YourMainDataTable'[Date Column]) RETURN IF(CurrentDate <= MaxDate, 1, 0) This measure returns 1 for any date in your main data table that is on or before the date selected in the slicer, and 0 otherwise. Apply the Filter Measure to Visuals Add a Slicer visual to your report page, using the Date Column from the new Slicer Dates table (not your main data table). For each visual in your report that needs to respect this filter (e.g., charts, tables), drag the Filter Measure into the "Filters on this visual" or "Filters on this page" pane. Set the filter condition to show items when the value is is equal to 1. Now, when you select a date from the Slicer Dates slicer, all visuals on the page/report will only display data from that date and all prior historical dates. The report will dynamically update to reflect the snapshot of data as it was at that specific point in time.563Views1like1Comment
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.