Replay of live stream with Marco Russo and Alberto Ferrari from SQL BI.
Connect with SQLBI:
Website: https://sqlbi.com
YouTube: https://www.youtube.com/sqlbi
Marco Russo: https://twitter.com/marcorus
Alberto Ferrari: https://twitter.com/FerrariAlberto
Questions answered in stream
Q: Is it possible to show 2 lines on the line chart for 2 different months? If yes how do u ensure that each line shows data labels for only those many days in the month which exist in month? eg31, 30 (12:10)
Q: Is it possible to have a simple increment row number in a table visual? (1 to whatever regardless of sort order). I don’t think it is, but if so please tell us. (14:27)
Q: Can you declare a variable after the RETURN? And if yes, does doing so give it a different context? (Iām guessing yes but struggling wrapping my head around it.) (15:53)
Q: is there any limitations for number is queries merging in power queries especially with direct query mode from SQL?? (16:59)
Q: Why can’t I have an implicit measure with calculation groups? (20:16)
Q: Anyway to use what-if parameters in custom DAX columns not just measures? (23:36)
Q: How many tables process in parallel by default in Power BI desktop, the Service and Premium on a standard refresh. Can this be adjusted? (24:47)
Q: How to avoid bi-directional relationships especially when they are used as a filter? (26:24)
Q: Value encoding in fact surrogate key is resulting in the DAX queries hitting premium query memory limit, when dimension table is big. Hash encoding seems to work fine. Did anyone face this? (27:26)
Q: Is there an easy way to create modern data sources in Tabular Editor and do you prefer modern data sources over legacy when SQL Server is the underlying database? (32:22)
Q: Is it possible to calculate a running sum on a non-date column that has multiple values? (34:15)
Q: Is it necessary to put an IF(HASONEVALUE) before a SWITCH function? (35:20)
Q: Using Calc Groups, my Formula Engine utilization is approximately 90%. When a similar query is executed that does not include the Calc Group, a more desirable FE to SE ratio is achieved. This particular Calc Group has 22 calculation items within it.
Is this a known limitation of Power BI and Calculation Groups? What are your suggestions for optimizing Calculation Groups? (37:52)
Q: I have a 300m row fact table in the model with a customer surrogate key. The key column is used mostly on aggregation and cross filtering capabilities. The report is very slow due to this expensive key column. What are the alternative approaches? Multiple aggregation fact tables as per page design? (44:16)
Q: Why are there no WoW time intelligence like in Excel? (48:59)
Q: Is there a way to control the connection options (e.g the gateway cluster used) for a paginated report that is published to the service? (51:51)
Q: Req to add a bar to each chart with the “All member”, totaling group field values. Would you create a bridge/matching table with a bi-di relationship, or use a SWITCH statement with diff measures? (53:16)
Q: How would your DAX pattern for LY apply when not slicing by a date hierarchy? Should we check for both ISINSCOPE as well as ISFILTERED? Difference? (55:56)
Q: I’ve heard that lots of calculation groups can cause issues for the model from a performance issue. Patrick believes it shouldnāt. What are Marco and Alberto’s views on the performance limitations? (58:57)
Q: Is there a good way to share only the measure content separately from the data? I currently do it as a text file. (not pbix file or workspace data model) (1:00:42)
Q: Which one is better — Big Fat Measure with all calculations in it OR a consolidated one with calculations split into individual measures? (1:03:09)
Q: I need to test the performance effect of some potential schema changes to a 500m rows fact table and related 250m row dimension table. Is there any hope of a small scale test proving the impact? (1:07:06)
Other questions
Q: Can we add filter in power bi dashboard?
Q: Is there a way to dynamically change a chart axis scale, even pan/zoom?
Q: Could it be an option to have a session where you choose a topic and elaborate about it instead of receiving random questions?
Q: Can you set up a slicer hidden on RLS and only available to certain people?
Q: When would the premium per user and Power automate visual are expected?
Q: Which football team that they support in Italy? I wonder
Q: Multi select drill down. Is it coming to Power BI?
Q: Is there a way to give access to an overview chart with all the information but in details they are assign to RLS?
Q: Is there anybody out there had the chance to see the small multiples working on Power BI?
Q: Does the usage of a ton of ISINSCOPE hurt performance by any chance?
Q: Tabular Editor 3 – GitHub integration?
Q: How can developers concurrently work on a report?
Q: Iāve been doing all my work (modeling/aggregations) on Power Query in Excel before importing into Power BI. Should I start importing the tables and doing that directly in Power BI?
Q: How would you go about creating an aging balance (specifically for Accounts receivables) without any snapshots available i.e needs to be calculated dynamically for any date?
Q: I used a measure to create the visual but when i click that visual it doesn’t cross filter other visuals. why is that can u please tell me?
Q: What do you think about Power Bi consultants future since all these Programming languages and different platforms coming into the picture?
Q: Best way to solve the Many to Many Relationship?
Q: Is it possible to automate the deployment pipeline in Power BI service?
Q: I use ISINSCOPE on measures to protect users from filtering fact tables with incompatible dim tables. Will this negatively impact performance and if so, better way to achieve this protection?
Q: Relationship with integer vs Relationship with a String? Is there a difference especially when dealing with a million records or more?
Q: What-If params creates a table, in my use case it ranges from 0.00 to 100000.00 and the size is very high, is there any other way to get input from user in Power BI?
Q: ISO weeks in DAX? Have you done that?
Q: Can a DAX Puzzle be a question on the DA-100? If yes I need to get all DAX Puzzles to practice.
Q: I have a HANA table with 40 billion records. Can we have power bi with DirectQuery? Have you tried it?
Q: Will incremental refresh work if my query folding breaks after filtering to range??
Q: How do you handle a billion rows in Power BI?
Q: I am interested in learning a programming language, is there one you would recommend over others?
Q: Is there a better way to make a matrix show multiple measures with one measure per row other than creating a switch statement and a calculated table?
Q: How do you create segments using calculated measures? I tried using a sample work by Marco or Alberto but it wouldn’t work
Q: Any chance to export matrix visual into Excel similar as we could see in actual tabular or grid format?
Q: How can we refresh one table only in a dataset available in PBI service?
Q: Will there ever be virtual columns on Power BI?
Q: After adding several visuals to a page, you can (sometimes) no longer copy and paste a visual to another page. Any tips to remedy this problem?
Q: Is there a way to make map visuals transparent. This could be very very useful. Especially when showing heat maps and bubbles at the same time.
Q: Do you know how to automatically select the Top N of a value in a drop-down slicer (e.g. P07 in a range of periods where P07 is the latest) without having to touch it. Autoselect basically.
Q: What is the purpose of a Dataset Owner for a Power BI dataset on a workspace?
Q: Is it possible to do drillthrough filter via a slicer.
Q: How to increase the limit of Adobe analytics dimensions to more than 4 (current limitation) via Adobe PBI connector, if i need 8 then should i create 2 tables with 4 each, this may give wrong data
Q: I don’t have company account then how can I share my report into Power BI service
Q: How can I add in a slicer for only certain roles?
Q: I am really working on learning DAX, but I have found it slightly easier to learn it in PowerPivot. Are there are any drawbacks to this approach?
Q: For minimizing publishing time on a big model >10gb, should I use DAX w/ calculated columns / tables when needed or PQ? My queries from DB are all native query
Q: Is it better to connect power BI directly to SAP or generate reports and access locally? some of the measures donāt work when we connect directly to SAP.
Q: The opening and closing month changes every month depending on when mm is closing. is there a way to update in one place so that the date slicers in all the dashboards gets updated?
Q: What is your biggest frustration with DAX?
Q: For SSAS/AAS/Model.bim data model projects, the only reason I use Visual Studio is for the diagram view but Tabular Editor for most other design features. What do you use?
Q: Is there a way to replicate RLS from PBI into a PowerPivot model in Excel?