User Profile
Ghhousuddin
Resolver I
Joined 3 years ago
User Widgets
Contributions
Re: Deneb - Vega Lite Small Multiples
Yes, it is possible to create a small multiple visual using Vega-Lite that dynamically adjusts the size of the multiples to fit on one page without the need for scrolling. One way to achieve this is to use the `repeat` operator in Vega-Lite to create a grid of facets, where the number of rows and columns are determined dynamically based on the number of products being shown. Here's an example Vega-Lite specification that demonstrates this: ``` { "repeat": {"row": "product", "column": "year"}, "spec": { "mark": "line", "encoding": { "x": {"field": "month", "type": "ordinal"}, "y": {"field": "sales", "type": "quantitative"} } }, "data": { "values": [ {"product": "A", "year": 2020, "month": "Jan", "sales": 100}, {"product": "A", "year": 2020, "month": "Feb", "sales": 120}, {"product": "A", "year": 2020, "month": "Mar", "sales": 140}, {"product": "B", "year": 2020, "month": "Jan", "sales": 80}, {"product": "B", "year": 2020, "month": "Feb", "sales": 90}, {"product": "B", "year": 2020, "month": "Mar", "sales": 100}, {"product": "A", "year": 2021, "month": "Jan", "sales": 110}, {"product": "A", "year": 2021, "month": "Feb", "sales": 130}, {"product": "A", "year": 2021, "month": "Mar", "sales": 150}, {"product": "B", "year": 2021, "month": "Jan", "sales": 90}, {"product": "B", "year": 2021, "month": "Feb", "sales": 100}, {"product": "B", "year": 2021, "month": "Mar", "sales": 110} ] }, "config": { "view": {"stroke": "transparent"}, "axis": {"grid": false}, "scale": {"rangeStep": 20}, "facet": {"spacing": {"row": 10, "column": 10}} } } ``` In this example, the `repeat` operator is used to create a grid of facets based on the "product" and "year" fields. The `spec` property contains the visualization specification for each individual facet, which in this case is a line chart showing sales over time. The `config` property is used to configure the layout and spacing of the facets. The `view` property sets the stroke to transparent to remove the border around each facet. The `axis` property sets the grid to false to remove the axis lines between the facets. The `scale` property sets the rangeStep to 20, which controls the size of the facets. The `facet` property sets the spacing between the facets in both the row and column directions. With this approach, the size of the facets will dynamically adjust based on the number of products being shown, allowing all the facets to fit on one page without the need for scrolling.4.5KViews1like1CommentRe: Auto Adjusting Dates In Model
If you want to automatically adjust the dates in your Power BI model without having to manually update the source Excel file, you can use a combination of Power Query and DAX. Here's how you can do it: 1. In Power Query, create a new query that reads the source Excel file and adds a new column with the adjusted dates. You can use the `Date.AddMonths` function to add a fixed number of months to each date in your source data. ``` Adjusted Date = Date.AddMonths([Date], 1) ``` 2. Load the modified data into the Power BI model and create relationships between the date column in the source data and the adjusted date column in the new query. 3. In your DAX calculations, replace references to the original date column with references to the adjusted date column. For example, if you have a measure that calculates sales for the current month using the original date column, like this: ``` Sales Current Month = CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], DATE(YEAR(TODAY()), MONTH(TODAY()), 1), TODAY())) ``` You can update it to use the adjusted date column instead, like this: ``` Sales Current Month = CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Adjusted Date], DATE(YEAR(TODAY()), MONTH(TODAY()), 1), TODAY())) ``` This will ensure that the measure always calculates sales for the current month based on the adjusted dates, regardless of whether the source Excel file has been updated. 4. Optionally, you can hide the original date column in your visualizations to avoid confusion. To do this, go to the "Fields" pane, find the original date column, click on the ellipsis button next to it, and choose "Hide in report view". With these steps, your Power BI model should automatically adjust the dates in your data based on the formula you specified in Power Query, allowing your time intelligence calculations to remain relevant without having to manually update the source Excel file.923Views0likes0CommentsRe: How to automatically select today as the latest date in PowerBI Between Slicer?
To automatically select today's date as the latest date in a Power BI between slicer, you can use a combination of DAX and Power Query steps. Here's how to do it: 1. Create a measure in the model that returns today's date: ``` Today = TODAY() ``` 2. Create a new query by going to "Home" > "New Source" > "Blank Query". 3. In the "Query Editor", click on "View" > "Advanced Editor". 4. In the "Advanced Editor", paste the following M code: ``` let Today = DateTime.LocalNow(), StartOfYear = Date.StartOfYear(Today), EndDate = Date.AddDays(Today, -1), Source = #table({"Start Date", "End Date"}, {{StartOfYear, EndDate}}) in Source ``` This code creates a table with two columns, "Start Date" and "End Date", that represent the default date range of the slicer. The "Start Date" is set to the start of the current year, and the "End Date" is set to yesterday's date. 5. Click on "Close & Load" to load the query into the model. 6. Create a between slicer using the "Start Date" and "End Date" columns from the new query. 7. Set the default selection for the slicer to "Between" and use the "Today" measure as the maximum value for the "End Date" field. To do this, go to the "Visualizations" pane, click on the slicer, and expand the "Format" section. Under "Default selection", choose "Between" and set the "End Date" field to use the "Today" measure. ``` Today = TODAY() ``` This will ensure that the slicer automatically selects today's date as the latest date when the report is refreshed. With these steps, the between slicer will default to showing the date range of the current year, with today's date as the latest date, when the report is refreshed.23KViews0likes1CommentRe: Changing low counts to mask/remove potential identification.
To display the student count as "<10" in Power BI Desktop when the count is less than 10, you can use the following formula in a measure: ``` Student Count = IF( COUNTROWS('Sheet1') < 10, "<10", FORMAT(COUNTROWS('Sheet1'), "0") ) ``` This formula first checks if the count of rows in the table is less than 10. If so, it returns "<10". If the count is 10 or greater, it formats the count as an integer with zero decimal places using the `FORMAT` function. To ensure that the "<10" value is sorted correctly in your table, you can set the sort order of the Student Count column to be based on the values rather than the alphabetical order. To do this, select the Student Count column in the table, click on the "Modeling" tab in the ribbon, and select "Sort by Column" from the "Column tools" section. Then choose "Student Count" from the list of available columns. Alternatively, you can create a separate column in your table that contains a numeric version of the Student Count measure. You can then sort the table by this column while still displaying the "<10" values in the Student Count column. Here's an example formula for the numeric version of the Student Count measure: ``` Student Count Numeric = IF( COUNTROWS('Sheet1') < 10, BLANK(), COUNTROWS('Sheet1') ) ``` This formula returns a blank value when the count is less than 10, and the actual count when it's 10 or greater. You can then set the sort order of the table based on this column while still displaying the "<10" values in the Student Count column.1.7KViews0likes1CommentRe: Query works in Oracle SQL, but PowerBI returns nothing
The Microsoft documentation you referred to is correct in stating that Oracle database native queries are supported in Power Query. However, there are still some limitations and differences between the SQL syntax and features supported in Oracle and those supported in Power Query, which can lead to some queries not working as expected. In your case, it's possible that the "CREATE DATE LIKE" syntax is not fully supported in Power Query, which is why it was causing issues. While this syntax works in Oracle SQL Developer, it may not be fully compatible with Power Query's implementation of Oracle database queries. In general, it's always a good idea to test your queries in Power Query before relying on them in your reports or dashboards. This can help you identify any compatibility issues or syntax differences that may exist between your SQL source and Power Query. If you're looking for additional sources of information on using native database queries in Power Query, you can refer to the official Microsoft documentation or community forums These resources can provide additional tips, best practices, and troubleshooting advice for working with native database queries in Power Query.10KViews1like0CommentsRe: Need help with filtering data between two tables
It sounds like the stacked column chart is not correctly aggregating the data across the selected months. To get the desired result, you need to ensure that the chart is summing the values for each month across all selected months, rather than just showing the values for the selected months individually. Here's how you can modify your chart to display the correct data: 1. Open the stacked column chart in Power BI Desktop. 2. Click on the "Values" field in the "Visualizations" pane and select "Value field settings". 3. In the "Value field settings" dialog box, select "Sum" as the aggregation method for the "Time Saved" field. 4. Click OK to close the dialog box. 5. Click on the "Axis" field in the "Visualizations" pane and select "Month" as the category axis. 6. Select the "Data" view from the top of the report canvas to switch to the data view. 7. In the data view, select the "Time Saved" column and click on the "Modeling" tab in the ribbon. 8. Select "New measure" from the "Calculations" dropdown menu. 9. In the "New measure" dialog box, enter the following formula: `Time Saved (All) = CALCULATE(SUM(Table1[Time Saved]), ALL(Table1))` 10. Click OK to create the measure. 11. Go back to the stacked column chart and click on the "Values" field in the "Visualizations" pane. 12. Select "Time Saved (All)" from the list of available measures. 13. Preview the chart to see the correct data for the selected months. This should ensure that the chart is correctly summing the data for each month across all selected months, rather than just showing the data for each selected month individually.1.1KViews0likes0CommentsRe: Error after 'Close and Apply' in Editor
The error message you received indicates that the sort operator in your query has exceeded the memory budget and the query execution has exceeded the allowed limits. This can occur if your dataset is very large or if the query is complex and requires a lot of memory. Here are some steps you can take to overcome this issue: 1. Optimize your query: Review your query and see if there are any areas that can be optimized. For example, you may be able to reduce the number of columns being returned or the amount of data being processed. You can also try to simplify the query by breaking it down into smaller, more manageable steps. 2. Increase memory limit: You can increase the memory limit for Power Query by going to the "File" menu, selecting "Options and settings", and then selecting "Options". Under the "Current File" tab, select "Data Load" and increase the "Maximum allowed memory usage" setting. This will allow Power Query to use more memory to complete the query. 3. Use incremental loading: If your data source supports incremental loading, consider using this option to load only the new or changed data instead of loading the entire dataset every time. 4. Use DirectQuery mode: If your data source supports DirectQuery mode, consider using this option to offload the processing to the database engine and avoid loading large amounts of data into memory. 5. Upgrade hardware: If your query is still exceeding the memory limits after optimizing and increasing memory, you may need to upgrade your hardware to provide more memory and processing power. Try these steps and see if they help to overcome the issue. If the problem persists, you may need to seek additional help or support from your IT department or the software vendor.582Views0likes0CommentsRe: Report Builder Pie Charts
Yes, it is possible to add a data label in a Power BI Report Builder pie chart that shows both the percentage and label (or category of the slice). Here are the steps to achieve this: 1. Add a pie chart to your report in Power BI Report Builder. 2. Select the pie chart and go to the "Format" tab in the ribbon. 3. Expand the "Data Labels" section and select "Value and Percentage" under the "Label Contains" dropdown menu. 4. Under "Value and Percentage", select "Percentage" and "Category Name" checkboxes to show both the percentage and label for each slice. 5. Customize the font, color, and position of the data labels as desired. 6. Preview the report to see the pie chart with the data labels that show both the percentage and label for each slice. Note that the appearance and functionality of data labels in Power BI Report Builder may vary depending on the version you are using, so some of the above steps may differ slightly. However, the basic principle of showing both percentage and label in a pie chart data label should remain the same.1.3KViews0likes0CommentsRe: Why a tabular model defined in DAX can be queried using MDX?
A tabular model defined in DAX can be queried using MDX because Microsoft has added support for the Multidimensional Expressions (MDX) language in Analysis Services Tabular mode starting with SQL Server 2012. This was done to allow existing MDX-based client tools, such as Excel PivotTables, to connect to and query a Tabular model. There are several use cases for querying a Tabular model using MDX: 1. Compatibility with existing client tools: As mentioned above, some client tools, such as Excel PivotTables, are designed to work with MDX and may not support DAX. By providing support for MDX in Tabular mode, Microsoft allows these tools to work with Tabular models. 2. Cross-compatibility with other data sources: MDX is a widely used language for querying multidimensional data sources, such as OLAP cubes. By providing support for MDX in Tabular mode, Microsoft allows Tabular models to be queried using the same language as other data sources, which can simplify integration and interoperability. 3. Advanced calculations: While DAX is a powerful language for defining calculations in a Tabular model, MDX provides more advanced functionality for complex calculations and analysis. In some cases, it may be more efficient or easier to define a calculation using MDX rather than DAX. 4. Backward compatibility: Some organizations may have existing MDX-based reports or queries that need to be migrated to a Tabular model. By providing support for MDX in Tabular mode, Microsoft makes it possible to migrate these queries without having to rewrite them in DAX. In summary, while DAX is the primary language for defining calculations in a Tabular model, MDX provides compatibility with existing client tools, cross-compatibility with other data sources, advanced calculations, and backward compatibility with existing queries.2.3KViews1like0CommentsRe: Json
Yes, you can use the "Combine & Transform Data" option in Power Query Editor to load multiple JSON objects from a single JSON file. Here's how you can do it: 1. In Power BI Desktop, select "Get Data" from the Home tab of the ribbon. 2. Select "JSON" from the list of available data sources. 3. In the file selection dialog box, browse to and select the JSON file that contains multiple JSON objects. 4. In the Navigator dialog box, select the "Combine & Transform Data" option from the bottom of the preview pane. 5. In the Combine Files dialog box, select the "JSON" option. 6. In the JSON Options dialog box, select the appropriate options for your file, such as whether the file has a root node or not. 7. Click OK to close the dialog boxes and load the data into Power Query Editor. Power Query Editor will then attempt to combine and transform the data from all the JSON objects in the file into a single table. You can then use the editor to perform any necessary transformations and data cleaning. If you encounter any errors or issues during the loading process, you can try adjusting the JSON options or performing additional data transformations to resolve them.506Views0likes0Comments
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.