How to repeat the List of items for each Category using M-Query in Power BIScenario:Suppose we have the sample of Product details as per below. In this sample we have 10 distinct Products and 3 unique Product Segments.From this sample, we need to repeat the list of Products for each Product Segment.We can achieve this Scenario, using the following M-Query (Power Query):ProdListBySegment =let ..read more

How to highlight Visual rows based on Selected values from Slicer in Power BIScenario:Suppose we have a simple data Model as follows:The Table visual built on this Model as follows:Now we want to highlight (NOT filtering) the rows on this table, based on the selected values from a Product Name Slicer.We can achieve this Scenario, by creating a disconnected (NOT related to the Model) table with a ..read more

How to return Max Sale Date and Product with correct Totals using DAX in Power BISuppose we have simple Data Modal as follows.The sample Data from ds_Sample is as follows:----------------------------------------------------------------Scenario 1:Returning the Max Sale Date, and the Product that contributing the Max SalesWe can achieve this Scenario using the following DAX Functions.Net ..read more

How to Calculate the Average of Main Category based on Averages of Sub Category using DAX in Power BIScenario:We have sample Dataset as follows, with various Dimension levels like:Region > Country > Product_Segment > ProductThis sample dataset has the Quantity of Products sold value (Qnty_Sold) either by Online or Offline (Sale_Mode).Based on this Dataset, first we need to Calculate the ..read more

How to calculate the ROLLUP Sub Totals and Grand Total Count, Sum and Average values for each Dimension Level in SQL ServerScenario:Suppose we have a sample table with Price values for different Hierarchy Levels like Region, Country, ProdGroup, ProdName, as shown below./* Drop the table if it arleady existsDROP TABLE IF EXISTS [dbo].tbl_Sample;*//* Create a Sample table */CREATE TABLE [dbo ..read more

How to return Minimum and Maximum value across multiple Columns in SQL ServerScenario:Suppose we have a sample Products table with Price values across multiple Years as shown below./* Create a sample table */CREATE TABLE [dbo].[tbl_Products]  (    [ProdID] [int] NOT NULL,    [ProdName] [varchar](255) NOT NULL,    [YR2010] [int] NULL,    [2011] [int ..read more

How to Aggregate or Concatenate the Numbers and Strings using STRING_AGG Function in SQL ServerScenario:Suppose we a sample Categories table as follows.. /* Create sample Categories table */CREATE TABLE [dbo].[tbl_Categories]    (        [CatID] [int] NULL,        [MainCat] [varchar](5) NULL,        [SubCat] [varchar ..read more

How to use PATINDEX Function to Remove the Special Characters from a Column in SQL ServerScenario:Suppose we have a sample data with Special Charaters as shown below:1!2@3#4$5%6^7&8*9(0)_+,<.€25>/?\|ABC$123.45abc|€987.45$123.45Gold@$916.75We need to remove the Special Characters like !@#$%^&*()_+,<€>/?\| , and clean the data as per below ..read more

How to create Root Node and Leaf Node Hierarchy using Recursive CTE in SQL ServerScenario:Suppose we have a sample data with ParentNode and ChildNode columns as follows.Lets say:A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.B1 is the Parent Node for {C1, D1}.C1 is the Parent Node for {E1, F1}.Please note that, the ..read more

How to create Root Node and Leaf Node Hierarchy using M-Query in Power BIScenario:Suppose we have a sample data with ParentNode and ChildNode columns as follows.Lets say:A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.B1 is the Parent Node for {C1, D1, E1,F1}.C1 is the Parent Node for {G1, H1}.Please note that, the ..read more