Power BI – SharePoint & Microsoft Power Platform Tutorials – SPGuides

How to Filter Last N Days Data Using Power BI DAX?

Tue, 30 Jul 2024 08:05:55 +0000

Last week, I worked on a Power BI customer support report that required showing the number of support tickets created in the last 30 days. Also, it filters the data to show the tickets created in the previous seven days. All these things are possible by using the Power BI DAX statement.

In this Power BI article, I will show you how to filter last N days data using Power BI DAX, such as filtering the last 30 days’ data and the last 7 days’ data using various methods.

Filter Last 30 Days Data Using Power BI DAX [Using Date Field]

While working on a Power BI support ticket activity report, I was required to show the number of support tickets created in the last 30 days. The data includes a ‘CreatedDate’ column, which records each ticket’s creation date.

For this example, I created a table called SupportTickets, which includes the TicketID and CreatedDate columns.

Let’s follow the below steps:

Open Power BI Desktop and load the above table into Power. Then, you can see data in the data pane.

Now, create a Power BI table visual to show all columns in the SupportTickets data set.

Here, we want to see the last 30 days, so we need to create a measure using the DAX expression below.

TicketsLast30Days = 
CALCULATE(
    COUNT('SupportTickets'[TicketID]),
    FILTER(
        'SupportTickets',
        'SupportTickets'[CreatedDate] >= TODAY() - 30 &&
        'SupportTickets'[CreatedDate] <= TODAY()
    )
)

Where:

TicketsLast30Days: This is the name of the measure we are creating.
CALCULATE: This function changes the context in which data is evaluated. It’s used to perform calculations with specific filters.
COUNT(‘SupportTickets'[TicketID]): This counts the number of rows in the ‘SupportTickets’ table where there is a value in the [TicketID] column.
FILTER: This function creates a subset of data based on a condition. In this case, it’s filtering the ‘SupportTickets’ table to include only tickets created within the last 30 days.
‘SupportTickets'[CreatedDate] >= TODAY() – 30 && ‘SupportTickets'[CreatedDate] <= TODAY(): This is the condition used in the FILTER function. It selects tickets where the [CreatedDate] is between today and 30 days ago.

Add the TicketsLast30Days measure to the table visual you created above. The Power BI table visual will then show only the last 30-day dates.

You can use the Power BI card visual to see the total count of tickets that were created in the last 30 days.

Filter Latest Date Using Power BI DAX [Using Date Column]

Suppose you are analyzing sales data for a retail company. You have a large dataset in Power BI with a column named SaleDate that records the dates of all sales transactions. Your task is determining the latest sale date from this dataset to identify the most recent sales activity.

For this example, I have a data table called SalesDate, which includes the Order Date, Sales Amount, Product Category, and Product Name columns.

Now follow the below steps:

Open Power BI Desktop and load the above table into Power. Then, you can see data in the data pane.

Now click the New measure under the Modeling tab to create a DAX measure to find the most recent sale date in the SaleDate column.

Then, in the formula bar, put the below DAX expression.

LatestOrderDate = MAX(SalesData[Order Date])

Where:

LatestOrderDate: This is the name of the measure we are creating.
MAX(SalesData[Order Date]): This function finds the maximum (latest) date in the [Order Date] column of the ‘SalesData’ table.

Then, add a Power BI Card visual to see the LatestOrderDate from the SalesTable.

In the above, SalesTable 26-07-2024 is the LatestOrderDate.

Filter Last 7 Days Data Using Power BI DAX

I will tell you how we calculated the last seven days’ sales in a data table using Power BI DAX.

I used the same SalesTable above but added another column showing the last order date. You can create the same column in the above example but only create a new column instead of Creating a measure.

Now follow the below steps:

Go to the Table view, then under the Table tools, click New column.

In the formula bar, put the DAX expression below.

last 7 days = IF('SalesData'[Order Date] <= [last order date] && 'SalesData'[Order Date] >= [last order date]-7,1,0)

Where:

last 7 days: This is the measure we are creating.
IF: This function checks a condition and returns one value if the condition is true and another value if it is false.
‘SalesData'[Order Date] <= [last order date] && ‘SalesData'[Order Date] >= [last order date] – 7: This condition checks if the order date is within the last 7 days from the [last order date]. It includes dates that are up to 7 days before the [last order date] and up to the [last order date].
1,0: If the condition is true (the order date is within the last 7 days), it returns 1. If the condition is false, it returns 0.

Then go to the Report view, create a Power BI table visual, and add Order Date, Sales Amount, Product Category, and Product Name columns.

Then, add a Power BI slicer visual and add the “last 7 days” measure to it.

When you select 1 from the slicer, you can see the table visual displaying the last 7 days’ orders.

You can filter the last seven days’ sales in a data table using Power BI DAX.

I hope you follow all the steps to filter n days using Power BI DAX.

Some more Power BI articles you may also like:

Bijay Kumar

I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com

www.enjoysharepoint.com

]]>

How to Filter Current Year Data Using Power BI DAX?

Tue, 30 Jul 2024 06:35:21 +0000

Recently, I was working on a Power BI report and needed to filter data for the current year. This is a common requirement for many reports, as it helps in analyzing and visualizing data to the ongoing time period. To achieve this, we can use DAX (Data Analysis Expressions) in Power BI to dynamically filter data based on the current year.

In this Power BI tutorial, I will show you how to filter current year data using Power BI DAX. Along with that, we will see how to filter last year data and calculate Year-to-Date (YTD) sales using Power BI DAX.

Filter Current Year Data Using Power BI DAX

Last week, I was working on a Power BI report that needed to show sales data only for the current year. To achieve this, I used DAX to create a measure that filters the data based on the year. Now, follow the below steps to see how I created it.

For this example, I created a data set that contains SaleID, Date, and Amount columns. You can take your own data set like the one below.

Load the data to the Power BI desktop using the get data option. Then, create a new measure and apply the formula below to find the current year’s sales value.

Sales_CurrentYear = 
CALCULATE(
    SUM(SalesTable[Amount]), 
    YEAR(SalesTable[Date]) = YEAR(TODAY())
)

Where:

Sales_CurrentYear: This is the name of the measure.
CALCULATE: This function modifies the context in which the data is evaluated.
SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
YEAR(SalesTable[Date]) = YEAR(TODAY()): This filter condition ensures that only sales from the current year are included. YEAR(TODAY()) gets the current year, and YEAR(SalesTable[Date]) extracts the year from the sales date.

Now, in the Report view, add a Power BI card visual to show the current year’s total sales.

This way, you can see the current year’s value in your Power BI Visual and check whether it is correct by adding another card visual that shows total sales.

Filter Last Year Data using Power BI DAX

Now, we will see how to filter last year’s data using Power BI DAX. We will use the same dataset as above. The table contains data for only two years: 2023 and 2024. In the previous example, we looked at the 2024 sales. Now, let’s see how to get the 2023 sales using Power BI DAX, which was last year.

Create a new measure and apply the formula below to find the last year’s sales value.

Sales_LastYear = 
CALCULATE(
    SUM(SalesTable[Amount]), 
    YEAR(SalesTable[Date]) = YEAR(TODAY()) - 1
)

Where:

Sales_LastYear: This is the name of the measure.
CALCULATE: This function changes the context in which the data is evaluated.
SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
YEAR(SalesTable[Date]) = YEAR(TODAY()) – 1: This filter condition ensures that only sales from the previous year are included. YEAR(TODAY()) – 1 gets the previous year, and YEAR(SalesTable[Date]) extracts the year from the sales date.

Now, in the Report view, add a Power BI card visual to show the last year’s total sales.

Filter Year to Date Using Power BI DAX

While working on a Power BI report, I needed to show the total sales amount from the beginning of the current year up to today’s date. To achieve this, I used Power BI DAX TOTALYTD() to create a measure that calculates the Year-to-Date (YTD) sales.

For this example, I am using the same data set, but I added some more rows to it.

Now follow the below steps to calculate Year to Date (YTD) values in Power BI.

I hope you loaded the above data set into Power BI Desktop. Now, create a new table using the DAX expression below.

DateTable = CALENDAR(MIN(SalesTable[Date]), MAX(SalesTable[Date]))

Where:

DateTable: This is the name of the calculated table.
CALENDAR: This function creates a table with a single column of dates.
MIN(SalesTable[Date]): This function finds the earliest date in the [Date] column of the ‘SalesTable’.
MAX(SalesTable[Date]): This function finds the latest date in the [Date] column of the ‘SalesTable’.

Go to the Model view and create a relationship between two data sets, i.e., the Sales[Date] column and the DateTable[Date] column.

Now go to Report view and create a New measure to show Year-to-Date (YTD) sales using the DAX expression below.

Sales Amount YTD = TOTALYTD(SUM(SalesTable[Amount]),'SalesTable'[Date])

Where:

Sales Amount YTD: This is the name of the measure.
TOTALYTD: This function calculates the year-to-date total for a given expression up to the last date in the specified date column, considering the current filter context.
SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
‘SalesTable'[Date]: This specifies the date column in the ‘SalesTable’ to consider for the year-to-date calculation.

Add a Power BI Table visual to the Report view, then add a date column from the data table and add the Sales Amount and Sales Amount YTD from SalesTable.

This is how you can filter year-to-date using Power BI DAX.

In this Power BI tutorial, we learned how to use Power BI DAX to filter data for the current year and the previous year. Additionally, we covered how to calculate Year-to-Date (YTD) sales using Power BI.

Also, you may like some more Power BI articles:

Bijay Kumar

www.enjoysharepoint.com

]]>

How to Use DATEDIFF() in Power BI DAX? [With Conditions]

Mon, 29 Jul 2024 13:55:49 +0000

Last week, while working on a Power BI dashboard, I needed to calculate the difference between two dates using DAX. I researched this and found that the Power BI DATEDIFF() function is the best option for this purpose.

In this Power BI article, I will explain the Power BI DATEDIFF() function and its syntax. We will also see how to use DATEDIFF() in Power BI DAX and how to filter DATEDIFF() with conditions in Power BI DAX, with examples.

DATEDIFF() in Power BI DAX

Power BI DATEDIFF() in DAX helps to calculate the difference between two dates and returns the amount of time between them in the specified Interval, such as days, months, weeks, quarters, seconds, minutes, hours, or years.

The only requirements are that you know the start and end points in date or time format and that you can specify the type of interval you want.

Power BI DATEDIFF() Syntax:

DATEDIFF(Date1, Date2, Interval)

Where:

Date1: The starting date.
Date2: The ending date.
Interval: The Interval of time you want the difference in, such as DAY, SECOND, MINUTE, HOUR, WEEKS, MONTH, QUARTER, or YEAR.

For example, if you want to find the number of days between two dates:

DATEDIFF('Table'[StartDate], 'Table'[EndDate], DAY)

This will give you the number of days between the StartDate and EndDate.

How to Use DATEDIFF() in Power BI DAX

Here, we will see how to use the DATEDIFF() function in Power BI DAX with the help of an example where we need to calculate the time to ship day using the order date and ship date.

For this example, I create a data set in Power BI that contains OrderID, CustomerName, OrderDate, ShipDate, Product, Quantity, and Price.

Now follow the below steps:

If you follow the same example, you loaded the above data set into Power BI Desktop. Then, you can see the column in the data pane.

Now go to Table view and click New column under the Table tools.

Then, in the formula bar, put the below DAX expression.

TimeToShip = DATEDIFF('Orders Table'[OrderDate], 'Orders Table'[ShipDate], DAY)

Where:

TimeToShip: This is the name of the calculated column.
DATEDIFF: This function calculates the difference between two dates.
‘Orders Table'[OrderDate]: This is the starting date, the date when the order was placed.
‘Orders Table'[ShipDate]: This is the ending date, the date when the order was shipped.
DAY: This specifies that the difference should be calculated in days.

The table view shows the TimeToShip created, which is the difference between the order date and the ship date.

DATEDIFF() With Condition in Power BI DAX

Last week, I was working on a Power BI report for a project management dashboard. I received a requirement to calculate the number of days between the project start date and the project end date, but only for projects marked as “Completed.” So, let’s see how we can do it.

According to this scenario, I have a SharePoint list named ProwerPlatformProject that contains the following columns with various data types:

Columns	Data Types
ProjectID	Number
ProjectName (Title)	Single line of text
StartDate	Date and time
EndDate	Data and time
Status	Choice

Now load the above SharePoint list into Power BI Desktop. The column will appear in the data pane.

Now go to Table view and click New column under the Table tools.

Then, in the formula bar, put the below DAX expression.

ProjectDuration = 
IF(
    PowerPlatformProjects[Status] = "Completed", 
    DATEDIFF(PowerPlatformProjects[StartDate], PowerPlatformProjects[EndDate], DAY), 
    BLANK()
)

Where:

ProjectDuration: This is the name of the calculated column.
IF: This function checks a condition and returns one value if the condition is true and another value if it is false.
PowerPlatformProjects[Status] = “Completed”: This condition checks if the project status is “Completed.”
DATEDIFF(PowerPlatformProjects[StartDate], PowerPlatformProjects[EndDate], DAY): If the project is completed, this calculates the number of days between the project’s start date and end date.
BLANK(): If the project status is not “Completed,” this returns a blank value.

Then, in the table view, a new column (ProjectDuration) was added to the data set.

Now, you can use this ProjectDuration column in your Power BI visualizations to analyze the duration of completed projects. This way, you can use DATEDIFF() in Power BI using DAX.

I hope you follow all the steps and have an idea of how to use DATEDIFF() in Power BI.

Also, you may like the below Power BI Tutorials:

Bijay Kumar

www.enjoysharepoint.com

]]>

Power BI DAX Date Filter Not Working [3 Main Reasons & Its Solutions]

Fri, 26 Jul 2024 06:55:14 +0000

When working with Power BI, you might encounter issues with date filters that do not work as expected. There can be various reasons for this, such as incorrect DAX formulas, wrong date formats, an improperly set date table, or missing relationships between the date table and data table.

Below, I will present some common problems and their solutions based on my experience working with Power BI DAX date filters.

Date Format Issues in Power BI

While working on Power BI, I was required to create a report that analyzed sales data over time. After loading the data into Power BI, I created a Power BI measure to filter the date column, but it did not give me the exact output I required. The DAX formula was correct when I went to the Power Query Editor. Then I saw that the Date column is a text data type, so we need to change it to a date data type.

Follow the below steps to change the datatype in Power Query Editor:

1. Under the Home tab, click Transform date.

2. Select the date column in the Power Query editor, then click the ABC icon and select Date.

3. Then you can see that our date column data type is Date.

This way, you can change the data format in Power BI using Power Query Editor.

Date Table Missing in Power BI

Sometimes, we do not have a date table while working on Power BI. Follow the steps below to create a date table in Power BI.

Go to the “Modeling” tab, select “New Table,” and use the following DAX formula:

DateTable = CALENDAR(DATE(Year,Month,Day),DATE(Year,Month,Day))

Where:

DateTable = This is the name of the new table.
CALENDAR = This function generates a list of continuous dates. Here, we must give starting and ending dates inside the CALENDAR function.
DATE(Year, Month, Day) = Here, we use the date function to give any specific date.

DateTable = CALENDAR(MIN(YourTable[DateColumn]), MAX(YourTable[DateColumn]))

Where:

DateTable = This is the name of the new table.
CALENDAR = This function generates a list of continuous dates between two specified dates.
MIN(YourTable[DateColumn]) = This calculates the earliest date in the DateColumn of YourTable.
MAX(YourTable[DateColumn]) = This calculates the latest date in the DateColumn of YourTable.

Relationships Not Set in Power BI

Last week, I worked on a Power BI report where I created a date table but didn’t check the relationship between the two tables. As a result, when creating a calculated column, it showed a #error value. After investigating, I discovered that the relationships between the two tables were not set. Follow the steps below to set up the relationships.

Before setting the relationship, you check that the two tables have the same column or the same column value.

Here, I have a table In Power BI called Extended Sales, in which the date column states 30 April 2024 to 19 June 2024.

At the same time, I have a date table in the same report. The dates range from 1 April 2024 to 1 July 2024.

To create a relationship between two tables, Go to the “Model view,” where you can see two tables.

Then drag “Date” from the “Extended Sales” table and drop it into the “Date” column in the “DateTable.” Here, a New Relationship pop-up will open, and click save.

Then, you see one line connected between two tables, which shows that a relationship has been made.

This way, you resolve the “relationships not set” issue in Power BI.

Lastly, write the correct DAX formula when creating any measure, calculated column, or new table.

I hope this article helped you resolve the Power BI DAX filter issue. If this solution doesn’t work, feel free to ask in the comments box.

Some more Power BI articles you may also like:

Bijay Kumar

www.enjoysharepoint.com

]]>

How to Filter Date Using Power BI DAX + 7 Examples

Thu, 25 Jul 2024 12:07:27 +0000

While working together, one of my Power BI clients wanted to filter the data between two dates or based on the date selection. I’ve found that using the Power BI DAX function is extremely easy.

In this Power BI tutorial, I will explain how to filter date using Power BI DAX, Power BI DAX filter max date, Power BI DAX filter date range, and many more:

Filter Between Two Dates using Power BI DAX
Filter Date Greater Than Today Using Power BI DAX
Filter Date Less Than using Power BI DAX
Filter Max Date in Power BI using DAX
Filter Date Range using Power BI DAX
Filter Specific Date Power BI DAX

Filter Date Using Power BI DAX

Here, I will show you how to filter by month using Power BI DAX. Suppose you have a dataset where you store daily sales data and want to see the sales for the current month using Power BI.

For this example, I have a small dataset that includes the Date and Sale Amount.

Now follow the below steps:

1. Open Power BI Desktop and load the dataset. Then, you will see the dataset in the Data pane.

2. Then click New measure under the Modeling tab.

3. In the formula bar, put the DAX expression below.

Current Month Sales = 
CALCULATE(
    SUM('MonthlySales Table'[SalesAmount]),
    FILTER(
        'MonthlySales Table',
        MONTH('MonthlySales Table'[Date]) = MONTH(TODAY()) &&
        YEAR('MonthlySales Table'[Date]) = YEAR(TODAY())
    )
)

Where:

Current Month Sales = This names the new measure.
CALENDAR() = This function calculates the sum of SalesAmount for the filtered rows
SUM(MonthlySales Table[SalesAmount]) = This part sums up the SalesAmount column.
FILTER(‘MonthlySales Table’, …) = This function filters the MonthlySales Table.
MONTH(‘MonthlySales Table'[Date]) = MONTH(TODAY()) = This part ensures that only rows where the month of the Date column matches the current month are considered.
YEAR(‘MonthlySales Table'[Date]) = YEAR(TODAY()) = This part ensures that only rows where the year of the Date column matches the current year are considered.

4. To see the sales for the current month, add a Power BI Card visual and then add the “Current Month Sales” to it.

Filter Between Two Dates using Power BI DAX

Suppose you have a table with TransactionDate from April 25th, 2024, to June 5th, 2024, and a SalesAmount column.

We want the total sales between May 1st and May 31st.

To do this, follow the below steps:

1. I hope you loaded the above data set into the Power BI. Then, go to the “Modeling” tab in Power BI Desktop, click “New Measure“, and enter the following DAX expression.

Total Sales Between Dates = 
CALCULATE(
    SUM('Sales Transactions'[SalesAmount]),
    FILTER(
        'Sales Transactions',
        'Sales Transactions'[TransactionDate] >= DATE(2024, 5, 1) &&
        'Sales Transactions'[TransactionDate] <= DATE(2024, 5, 31)
    )
)

Where:

Total Sales Between Dates = This name is for the measure we’re creating, which will calculate the total sales amount between specific dates.
CALCULATE() = This function modifies the context in which the data is evaluated, allowing us to apply filters and perform calculations.
SUM(‘Sales Transactions'[SalesAmount]) = This part of the formula calculates the sum of the ‘SalesAmount’ column from the ‘Sales Transactions’ table.
FILTER() = This function returns a table that includes only the rows that meet the specified condition.
‘Sales Transactions’ = This specifies the table we’re filtering, which contains the sales transaction data.
‘Sales Transactions'[TransactionDate] >= DATE(2024, 5, 1) = This condition filters the rows to include only those where the ‘TransactionDate’ is on or after May 1, 2024.
‘Sales Transactions'[TransactionDate] <= DATE(2024, 5, 31) = This condition filters the rows to include only those where the ‘TransactionDate’ is on or before May 31, 2024.

2. You can create a card visual to see total sales between May 1st and May 31st.

This way, you can filter between two dates DAX in Power BI.

Filter Date Greater Than Today Using Power BI DAX

While working on Power BI, I was required to track the progress of various IT products. The company needed a way to monitor task deadlines to ensure product development remained on schedule.

The dataset was stored in the Power BI named ProductTimeline, which included TaskName, DueDate, Status, and AssignedTo columns.

Now follow the below steps to count the tasks left before the due date.

Go to Power BI Desktop and click New measure under the Modeling tab. Then, in the formula bar, put the below DAX expression.

TasksLeft = 
CALCULATE (
    COUNTROWS ( 'ProductTimeline' ),
    FILTER (
        ProductTimeline,
        ProductTimeline[DueDate] > TODAY()
    )
)

Where:

TasksLeft = This is the name of the new measure.
CALCULATE = This function modifies the context in which the data is evaluated, allowing you to perform calculations under specific conditions.
COUNTROWS (‘ProductTimeline’) = This counts the number of rows in the ProductTimeline table.
FILTER(ProductTimeline, ProductTimeline[DueDate] > TODAY()) = This part filters the ProductTimeline table to include only rows where the DueDate is greater than today’s date.

Now add a Power BI Card visual to see how many TasksLeft.

Filter Date Less Than using Power BI DAX

While working on Power BI, I was required to create a report displaying all sales transactions before a specific cutoff date. This was to help the sales team analyze past performance and trends leading up to that date.

According to the requirement, I have a data set that contains the Sale Date, Sales Amount, and Sales Req column.

Now, follow the below steps:

Make sure you’ve imported the data set into Power BI. Then, go to the ‘Modeling’ tab, click on ‘New Column’, and input the provided DAX formula.

FilteredSales = 
CALCULATE(
    SUM('Transaction Records'[sales Amount]), 
    FILTER(
        'Transaction Records',
        'Transaction Records'[Sale Date] < DATE(2024, 06, 02)
    )
)

Where:

FilteredSales = This is the name of the measure.
CALCULATE = This function changes the context for the calculation based on the given conditions.
SUM(‘Transaction Records'[Sales Amount]) = This sums up the values in the sales Amount column from the Transaction Records table.
FILTER(‘Transaction Records’, ‘Transaction Records'[Sale Date] < DATE(2024, 06, 02)) = This filters the Transaction Records table to include only those rows where the Sale Date is before June 2, 2024.

You can create a Power BI card visual to show FilteredSales.

Now, you can filter dates less than a specific date using Power BI DAX.

Filter Max Date in Power BI using DAX

While working on a sales performance report in Power BI, the management team needs to analyze sales data up to the most recent date available in the dataset. The report should automatically filter to show only the records from the latest date for each product category.

So, I thought I would show you how to filter for the maximum date in Power BI using DAX.

For this example, I have a data set that contains SaleDate, ProductCategory, SalesAmount, and QuantitySold columns.

Click the New measure in Power BI Desktop to get the latest date. Then, put the DAX expression below in the formula bar.

MaxDate = MAX(SalesRecords[SaleDate])

Where:

MaxDate = This is the name of the measure.
MAX(SalesRecords[SaleDate]) = This function finds the maximum value in the SaleDate column of the SalesRecords table.

Now, add a Power BI Table visual, and then add MaxDate, ProductCategory, SalesAmount, and QuantitySold columns.

The table visual shows only the latest date value. Using Power BI DAX, you can apply a filter based on the maximum date value.

Filter Date Range using Power BI DAX

While working on Power BI, I was required to create a report that filters employee records based on their date of joining. The goal was to display only those employees whose joining dates fall within a specific range from February 27, 2023, to February 20, 2024.

So, I created a Power BI table containing EmployeeID, EmployeeName, and Date of joining.

Now, Go to the Modeling tab on the top ribbon and click “New table” to create a new table using DAX. Then, in the formula bar, put the below DAX expression.

Range = 
VAR DateStart = DATE ( 2023, 2, 27 )
VAR DateEnd = DATE ( 2024, 2, 20 )
RETURN
    CALCULATETABLE (
        Employees,
        FILTER ( Employees, Employees[Date of Joining] <= DateEnd && Employees[Date of Joining] >= DateStart )
    )

Where,

Range: This is the name of the new.
VAR DateStart = DATE(2023, 2, 27): This defines a variable DateStart with the value February 27, 2023.
VAR DateEnd = DATE(2024, 2, 20): This defines a variable DateEnd with the value February 20, 2024.
RETURN: This specifies what the formula should return after the variables are defined.
CALCULATETABLE: This function creates a table by evaluating a table expression (in this case, Employees) under a modified filter context.
FILTER(Employees, Employees[Date of Joining] <= DateEnd && Employees[Date of Joining] >= DateStart): This filters the Employees table to include only those rows where the Date of Joining is between DateStart and DateEnd.

Then, a new table will be created in the Data pane under “Range.” Check the screenshot below.

This is how to apply the filter between the date ranges using Power BI DAX in Power BI.

Filter Specific Date Power BI DAX

While working on Power BI, I received a requirement to show data for a specific date range in my visual. Usually, I would use a slicer to filter the dates, but using a slicer can take up valuable space on the report canvas. So, I used a DAX formula to filter and show the specific data in the visual.

I will use the above data set containing EmployeeID, EmployeeName, and Date of joining columns.

Now, in the Power BI Desktop, using the below DAX expression, create a table:

Filtered Specific Date = 
CALCULATETABLE(
    Employees,
    Employees[Date of Joining] = DATE(2024,2,20)
)

Where:

Filtered Specific Date: This is the name of the new table.
CALCULATETABLE: This function creates a table by evaluating a table expression (in this case, Employees) under a modified filter context.
Employees: This is the table you are filtering.
Employees[Date of Joining] = DATE(2024, 2, 20): This condition filters the Employees table to include only those rows where the Date of Joining is exactly February 20, 2024.

This is how to apply the filter of the data tables only for the specific dates or the selected dates using the Power BI Dax.

This Power BI Tutorial explained how to filter the data by date using the Power BI DAX formula.

Also, you may like the below Power BI Tutorials:

Bijay Kumar

www.enjoysharepoint.com

]]>

How to Filter Distinct Date Using Power BI DAX?

Thu, 25 Jul 2024 12:06:01 +0000

Sometimes, you may need to display unique dates from a dataset in a Power BI report. You can create a calculated table or column that filters out duplicate dates to do this. Let’s look at an example of how to filter distinct date using Power BI DAX.

Filter Distinct Date Using Power BI DAX

While working on Power BI, I was required to create a new table from a table named DateTable that includes a column called Date containing various dates. The new table should only include a unique list of dates from before today.

Check the below screenshot for DateTable:

Now follow the below steps:

Open Power BI Desktop and load the above DateTable. Then, you can see the data presented in the Data pane.

Go to the Modeling tab and Click on New table.

In the formula bar, put the DAX expression below.

FilteredDates = 
VAR TodayDate = TODAY()
RETURN
DISTINCT (
    FILTER (
        'DateTable',
        'DateTable'[Date] < TodayDate
    )
)

Where:

FilteredDates: This is the name of the new table.
VAR TodayDate = TODAY(): This defines a variable TodayDate that holds today’s date.
RETURN: This specifies what the formula should return after defining the variables.
DISTINCT: This function returns only unique values in the resulting table.
FILTER(‘DateTable’, ‘DateTable'[Date] < TodayDate): This filters the DateTable to include only rows where the Date is less than today’s date.

Now, you can see that the table’s date column does not have repeated dates. This is how you can filter distinct dates using Power BI DAX.

Distinct Count of Values Based on Date using Power BI DAX

Recently, I received a requirement to analyze the number of unique IDs for specific dates and locations. The goal was to display a distinct count of IDs for the dates selected from a date slicer and a particular market location chosen from a location slicer.

I loaded a demo date set in Power BI according to the requirement.

Let’s follow the below steps to do this:

In the Report view, add two Power BI Slicer visuals. One is the Date Slicer, which shows all the dates, and the other is the Location Slicer, which shows all the Locations.

Go to the Modeling tab and Click on New measure. Then, in the formula bar, put the DAX expression below.

DistinctIDCount = 
VAR SelectedDates = VALUES('AssetTracking'[Date])
VAR SelectedLocation = SELECTEDVALUE('AssetTracking'[Location])
RETURN
    CALCULATE(
        DISTINCTCOUNT('AssetTracking'[ID]),
        'AssetTracking'[Date] IN SelectedDates,
        'AssetTracking'[Location] = SelectedLocation
    )

Where:

DistinctIDCount: This is the name of the new measure.
VAR SelectedDates = VALUES(‘AssetTracking'[Date]): This variable captures the list of unique dates currently selected in the AssetTracking table.
VAR SelectedLocation = SELECTEDVALUE(‘AssetTracking'[Location]): This variable captures the single selected value from the Location column in the AssetTracking table.
RETURN: This specifies what the formula should return after defining the variables.
CALCULATE: This function evaluates an expression in a modified filter context.
DISTINCTCOUNT(‘AssetTracking'[ID]): This expression counts the number of unique IDs in the AssetTracking table.
‘AssetTracking'[Date] IN SelectedDates: This condition ensures that only rows with dates that match the selected dates are considered.
‘AssetTracking'[Location] = SelectedLocation: This condition ensures that only rows with the selected location are considered.

Then, add a Power BI Card visual and the DistinctIDCount measure.

When you select any Date and Location from the Power BI Slicer, the distinct ID count is displayed in the Power BI card visual.

This way, you can distinguish the count of values based on a date using Power BI DAX.

Additionally, you may like some more Power BI articles:

Conclusion

I hope you followed all the steps and now know how to filter distinct dates in Power BI using DAX. This tutorial covered filtering by distinct dates using Power BI DAX and the distinct count of values based on dates using Power BI DAX.

Bijay Kumar

www.enjoysharepoint.com

]]>

How to Create a New Table From an Existing Table in Power BI?

Tue, 23 Jul 2024 11:37:17 +0000

Sometimes, you may receive a requirement to create a new table from an existing table in Power BI. This task can be for various reasons, such as organizing data more effectively or preparing it for specific analyses and visualizations.

So, I thought to show you how to create a table from an existing table in Power BI. Additionally, we will cover the following topics:

Create a table from another table with the condition in Power BI
Creates a table from another table with a filter in Power BI
Create a new table in Power BI from the existing two tables
Creates a table from another table in Power BI Power Query Editor
Create a new table in Power Query from the existing two tables
Creates a table from another table using Power BI DAX
Create a table from another table with distinct values in Power BI

Create a Table From an Existing Table in Power BI

While working on Power BI, I got a requirement for a company that sells various Software products worldwide. They want a list of all product categories and their total sales amounts in a Power BI report. So, in that case, creating a table from an existing table in Power BI is the best option. Now follow the below steps to do this:

According to this scenario, I have a SharePoint list named Sales Data that contains the following columns with various data types:

Columns	Data Types
Transaction ID	Single line of text
Product Name	Single line of text
Product Category	Single line of text
Sales Amount	Currency
Date	Date and time

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Under the “Modeling” tab, click “New table.”

3. Then, in the formula bar, put the below DAX expressions.

SummaryTable = SUMMARIZE('Sales Data', 'Sales Data'[Product Category], "Total Sales", SUM('Sales Data'[Sales Amount]))

Where:

SummaryTable = This names the table we’re creating, which summarizes sales data by product category.
SUMMARIZE() = This function creates a summary table by grouping data based on specified columns and calculating aggregations.
‘Sales Data’ = This specifies the table from which we summarize the data, in this case, the ‘Sales Data’ table.
‘Sales Data'[Product Category] = This specifies the column by which we are grouping the data. The ‘Product Category’ column is from the ‘Sales Data’ table.
“Total Sales” = This is the name of the new column in the summary table. It will contain the total sales amount for each category.
SUM(‘Sales Data'[Sales Amount]) = This is the aggregation calculation for the “Total Sales” column. It calculates the sum of the ‘Sales Amount’ column in the ‘Sales Data’ table for each group.

4. When you go to the table view and click on “SummaryTable,” you will see the sales data organized by product category.

Let’s look at another example: Create a table with Conditions in Power BI from another table.

Create a Table from Another Table with Condition in Power BI

Suppose you want to analyze sales performance by creating a separate table that only includes transactions where the sales amount is greater than 500.

According to this scenario, I have a data set in Power BI with transaction ID, customer ID, date, sales amount, and product ID, as shown in the screenshot below.

Now follow the below steps:

Under the “Modeling” tab, click “New table.” Then, in the formula bar, put the below DAX expressions.

HighValueSales = FILTER('Sales Table', 'Sales Table'[Sales Amount] > 500)

Where:

HighValueSales = This names the new table we’re creating, which will contain sales data for high-value transactions.
FILTER() = This function returns a table that includes only the rows that meet the specified condition.
‘Sales Table’ = This specifies the table we’re filtering, which contains the sales data.
‘Sales Table'[Sales Amount] > 500 = This is the condition we’re applying to the filter. It includes only rows where the ‘Sales Amount’ is greater than 500.

Go to the Table view, select HighValueSales in Data files, and look at the table to find sales amounts greater than 500.

This way, you can create a Table From Another Table with Condition in Power BI.

Create a Table From Another Table using Power BI DAX

Here, I have a dataset in Power BI that contains Product ID, Product Name, Category, Quantity Sold, and Unit Price. Please check the screenshot below.

Now follow the below steps:

Go to the Table view and click ‘New table‘ under the Home tab.

Then, in the formula bar, paste the following DAX expression.

SalesSummary = 
GROUPBY(
    'Product Sales',
    'Product Sales'[Category],
    "TotalSalesAmount", SUMX(CURRENTGROUP(), 'Product Sales'[Quantity Sold] * 'Product Sales'[Unit Price])
)

Where:

SalesSummary = This part names the table we create, summarizing sales data by category.
GROUPBY() = This function creates a summary table that groups data by one or more columns. It allows for custom aggregations within each group.
‘Product Sales'[Category] = This specifies the column by which we are grouping the data. In this case, it’s the ‘Category’ column from the ‘Product Sales’ table.
“TotalSalesAmount” = This is the name of the new column we’re creating within each group. It will contain the total sales amount for that category.
SUMX(CURRENTGROUP(), ‘Product Sales'[Quantity Sold] * ‘Product Sales'[Unit Price])= This part defines the calculation for the “TotalSalesAmount” column.
SUMX() = This function performs row-by-row calculations and then summarizes the results.
CURRENTGROUP() = This function returns the table that represents the current group being processed by GROUPBY.
‘Product Sales'[Quantity Sold] * ‘Product Sales'[Unit Price] = This multiplies the quantity sold by the unit price for each row in the current group to get the total sales amount for that row.

Then you can see in the table view a new table created.

Create a New Table From an Existing Table with Power BI Filter

Suppose you have a large dataset in Power BI containing information about all the sales transactions over the past and this year. Your manager wants to see data specifically focusing on the sales made in the last quarter (last three months).

Instead of manually filtering the data with the Power BI filter pane, We can create a new table that only includes sales data from the last quarter.

The screenshot below displays the dataset we’ve loaded into Power BI.

Now follow the below steps to do this:

Go to the Table view and click ‘New table‘ under the Home tab.

Then, in the formula bar at the top, enter a formula to filter the data for the last quarter. I am using the DAX expression below.

QuarterlySalesData = FILTER(Sales, Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY())-2, 1))

Where:

QuarterlySalesData = This name is for the new table we’re creating, which will contain sales data for the current quarter.
FILTER() = This function returns a table that includes only the rows that meet the specified condition.
Sales = This specifies the table we’re filtering, which contains the sales data.
Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY())-2, 1) = This condition filters the rows to include only those where the ‘Date’ column is on or after the first day of the current quarter.

In the table view, you will find a new table named QuarterlySalesData. It contains sales data from the last quarter.

This way, you can create a new table from an existing table with a filter in Power BI.

Create a New Table From an Existing Columns in Power BI

Suppose we have a data set that includes many columns, such as Date, Product, Quantity, Amount, Salesperson, Region, and Category.

For reporting purposes, you want to create a simplified version of this table that only includes the Date, Product, Quantity, and Amount columns.

To do this, follow the below steps:

Open the Power BI Desktop and load the above table. Then, under the Modeling tab, click the New table.

In the formula bar, put the below DAX expression:

Simplified_Sales = 
SELECTCOLUMNS(
    Sales_Details,
    "Date", Sales_Details[Date],
    "Product", Sales_Details[Product],
    "Quantity", Sales_Details[Quantity],
    "Amount", Sales_Details[Amount]
)

Where:

Simplified_Sales = This is the name of the new table we are creating.
SELECTCOLUMNS() = This function creates a new table with selected columns from an existing table and optionally renames these columns.
Sales_Details = This specifies the source table from which we are selecting columns.
“Date”, Sales_Details[Date] = This selects the ‘Date’ column from the Sales_Details table and renames it to “Date”.

When you go to the table view, you will see one table, Simplified_Sales.

Following the above steps, you can create a new table from an existing table in Power BI.

Create a New Table in Power BI From the Existing Two Tables

For this example, I have two tables in Power BI: one containing information about sales transactions (Transaction Table) and another containing details about products (Product Table). Now, I want to create a new table in Power BI that combines relevant information from both tables.

Transaction Table:

Product Table:

Now follow the below steps to create a new table:

First, go to the Model View and check whether the two tables establish a relationship. This is usually done automatically if there are common fields (like Product ID in this case) that can be used to establish the relationship.

If a relationship isn’t created automatically, click and drag the Product ID column from the Transaction Table to the Product ID column in the Products Table. A line will appear between the two tables, showing that a relationship has been made.

Then, inside the Model view, click ‘New Table‘ from the Home tab.

Then, in the formula bar, put the DAX expression below.

CombinedTable = 
SUMMARIZE(
   'Transaction Table',
   'Transaction Table'[Product ID],
   'Product Table'[Product Name],
   'Transaction Table'[Quantity],
   'Transaction Table'[Revenue],
   'Product Table'[Price]
)

Where:

CombinedTable = This name is for the table we’re creating, which combines data from the ‘Transaction Table’ and the ‘Product Table’.
SUMMARIZE() = This function creates a summary table by grouping data based on specified columns and calculating aggregations.
‘Transaction Table’ = This specifies the first table from which we summarize the data, in this case, the ‘Transaction Table’.
‘Transaction Table'[Product ID] = This specifies the column from the ‘Transaction Table’ by which we are grouping the data. It’s the ‘Transaction ID’ column.
‘Product Table'[Product Name], ‘Transaction Table'[Quantity], ‘Transaction Table'[Revenue], ‘Product Table'[Price] = These specify the additional columns we want to include in the summary table.

Then, you see a new table created in our data set.

Create a Table From Another Table using Power BI Power Query Editor

Suppose you have a data set in Power BI, like the screenshot below.

Now follow the below steps to do this:

Under the Home tab, click Transform date.

In the Power Query Editor, under the Transform tab, click Group By.

Once the Group By window opens, choose the “Basic” option. After that, select the “Date” columns. Next, name the column and choose “Sum” as the operation. Finally, select the “Quantity” column. Then click OK.

Then, you can see a new table in the Power Query Editor.

Create a New Table From Two Existing Tables in Power BI DAX

In this example, we have two tables: one for North sales and one for South sales. We will combine these tables to create one table using the Power BI DAX UNION function.

Sales_North:

Sales_South:

Now follow the below steps:

1. Open the Power BI Desktop and load the above table. Then, under the Modeling tab, click the New table.

2. In the formula bar, put the below DAX expression:

Combined_Sales = 
UNION(
    SELECTCOLUMNS(
        Sales_North,
        "Date", Sales_North[Date],
        "Product", Sales_North[Product],
        "Quantity", Sales_North[Quantity],
        "Amount", Sales_North[Amount]
    ),
    SELECTCOLUMNS(
        Sales_South,
        "Date", Sales_South[Date],
        "Product", Sales_South[Product],
        "Quantity", Sales_South[Quantity],
        "Amount", Sales_South[Amount]
    )
)

Where:

Combined_Sales = This is the name of the new table we are creating.
UNION() = This function combines the rows from two or more tables into a single table.
SELECTCOLUMNS() = This function selects specific columns from a table and optionally renames them.

3. When you go to table view you can see one table Combined_Sales.

Create a Table From Another Table With Distinct Values in Power BI

Suppose you have a dataset (‘Product Performance’) that includes information such as Product Category, Product Name, Sales Channel, and sales Amount.

Your goal is to create a table that shows each unique Product Category alongside the total sales Amount.

To do this, follow the below steps:

Open Power BI Desktop and load the dataset mentioned above. Then, go to the Modeling tab and click on ‘New table‘. In the formula bar, enter the following DAX expression.

DistinctTable = 
VAR DistinctValues =
    ADDCOLUMNS(
        DISTINCT('Product Performance'[Product Category]),
        "TotalAmount", CALCULATE(SUM('Product Performance'[Amount]))
    )
RETURN
    DistinctValues

Where:

DistinctTable = This is the name of the new table we are creating.
VAR DistinctValues = Defines a variable named DistinctValues.
ADDCOLUMNS() = Creates a new table by adding a calculated column to an existing table.
DISTINCT(‘Product Performance'[Product Category]) = Retrieves unique values from the ‘Product Category’ column in the ‘Product Performance’ table.
“TotalAmount” = Name for the new calculated column being added.
CALCULATE(SUM(‘Product Performance'[Amount])) = Calculates the total sum of the ‘Amount’ column from the ‘Product Performance’ table, considering any filters that might apply.
RETURN DistinctValues = Returns the table generated by the ADDCOLUMNS function, which includes distinct product categories and their corresponding total amounts.

You will now see the DistinctTable column added in the Data pane. Clicking on it will display the screenshot below.

This way, you can create a table from another with distinct values in Power BI.

Conclusion

I hope you follow all the above examples to create a new table in Power BI from another table.

In this tutorial, I covered how to create a table from another table in Power BI, including creating tables based on specific conditions, generating new tables from existing columns, filtering existing tables to create new ones, and combining two existing tables to form a new one.

Also, you may like:

Bijay Kumar

www.enjoysharepoint.com

]]>

How to Append Columns in Power BI using Power Query Editor?

Mon, 22 Jul 2024 05:15:15 +0000

While working on Power BI, I was required to create a report using sales data from January to December, with each table having the same columns. To create the report, we needed to combine this data, and I learned that we can use the Append Columns method in Power Query Editor. I thought I would share with you what Append Columns in Power Query Editor in Power BI is and how to use it to append columns.

Additionally, we will discuss the following topics:

Append Columns from the Same Table using Power BI Power Query Editor
Append Tables with different columns using Power BI Power Query Editor
Append Columns using Power BI DAX
Difference between Append vs Merge in Power BI using Power Query Editor

What is an Append Columns in Power BI Power Query Editor?

Power Query’s “Append Columns” in Power BI is a feature that allows you to combine data from two or more tables by adding rows from one table to another. In simple terms, it stacks the rows of one table on top of the rows of another table, creating a single, larger table.

For example, if you have two tables of sales data for different months, appending them will create a single table with combined sales data for both months.

This differs from merging tables in Power BI, which typically combine columns based on a common field.

Append Columns in Power Query in Power BI

Take a Scenario where you work for a retail company that tracks monthly sales data in separate tables for each month. You have two tables: one for January sales and another for February sales. You want to combine these tables into one table to analyze the total sales for the year’s first two months.

According to this scenario, we have two SharePoint lists named January Sales and February Sales that contain the following columns with various data types:

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Under the Home tab, click on Transform Data to open the Power Query Editor.

3. In the Power Query Editor, you will see your tables (e.g., JanuarySales and FebruarySales) listed in the Queries pane on the left.

4. Go to the Home tab, click on the Append Queries dropdown, and select Append Queries as New. This creates a new table from the appended data while keeping the original tables unchanged.

5. In the Append Queries dialog box, choose “Two tables” if you’re appending just the January and February sales tables. Then, select “January Sales” as the first table and “February Sales” as the second table. Finally, click OK.

6. A new query (e.g., Append1) will be created, containing the combined data from both tables. Review the data to ensure it has been appended correctly.

This is how to append columns using the Power Query editor in Power BI.

Append Columns from the Same Table in Power BI using Power Query

Here, we see how to append columns from the same table in the Power query editor.

Here, we have a table in Power BI, and I want to append a column.

Now follow the below steps:

Once the data is loaded, click on “Transform Data” to open the Power Query Editor. Next, navigate to the Home tab, click on the Append Queries dropdown, and choose “Append Queries as New”. This action creates a new table from the appended data, preserving the original tables.

In the Append Queries dialog box, choose “Two tables” since you’re appending data from the same table. Then, select the same table twice and click OK.

A new query will be created containing the combined data from both regions.

This way, you can append columns from the same table in Power Query Editor.

Append Tables With Different Columns in Power BI Power Query

When you append tables with different column names in Power Query, some table cells may display null values.

This occurs because Power Query attempts to match columns based on their names during the append operation.

If a column name in one table doesn’t exist in the other, there won’t be any data to fill those cells in the appended table, resulting in null values.

If you still need to append tables with different column names, you can rename the columns before performing the append operation.

Append Columns using Power BI DAX

This example shows how we can append columns without using the Power Query Editor.

Appending tables in Power BI without the Power Query Editor can be done using DAX (Data Analysis Expressions) in the Data Model. However, unlike Power Query, DAX doesn’t have a direct “append” function. Instead, you can achieve a similar result using the Power BI DAX UNION function.

We’re using the same table as before for the January and February sales. Now follow the below steps:

After loading the January and February sales data, click on “New table” under the Modeling tab.

Then, in the formula bar, put the below DAX expression.

AppendedTable = UNION('January Sales','February Sales')

Where:

AppendedTable = This is the name given to the new table we’re creating by appending data from two existing tables.
UNION() = This function combines the rows from two or more tables into a single table without duplicates.
‘January Sales’ = This specifies the first table we’re appending, which presumably contains sales data for the month of January.
‘February Sales’ = This specifies the second table we’re appending, which presumably contains sales data for the month of February.

When you go to the table view, you’ll see the AppendedTable, which contains data from both January and February sales.

This way, you can append columns without using the Power Query Editor.

Append vs Merge in Power BI and Power Query

The table below shows the contrast between the Power Query Append columns and the Power Query Merge columns.

Power Query Append	Power Query Merge
Combines rows from multiple tables vertically, stacking them on top of each other.	Combines columns from different tables horizontally based on a common key column(s).
Performs a union operation, simply stacking rows without considering common columns.	Performs a join operation, matching rows between tables based on specified key columns.
No specific key columns are required; it aligns columns based on name similarity.	Key columns must be specified from each table to perform the join operation.

I hope you follow all the steps above to append the column and know how to append it in Power BI.

Also, you may like some more Power BI Tutorials:

Bijay Kumar

www.enjoysharepoint.com

]]>

Power BI Sum Group by

Fri, 14 Jun 2024 07:12:56 +0000

This Power BI tutorial will teach us to sum and display values using the group by function and how to sum values with the Power Query group by function.

Additionally, we will discuss the topics below:

Power BI sum group by column
Power BI sum group by DAX
Power BI sum group by multiple columns
Power BI: measure sum by group
Power BI DAX sum group by with filter

Power BI Sum Group By Column

Imagine you’re an analyst for an online retail store. You want to understand your sales performance across different product categories. You have a SharePoint list with product details, such as category (e.g., Clothing, Electronics, Books, Beauty), Sales Quantity, and Sales Amount for each product.

You want to calculate the total sales for each product category.

Now follow the below steps to do this:

1. First, open Power BI Desktop and load the SharePoint list. Then, you will see the dataset in the Data pane.

2. Under the Home tab, click Transform date.

3. Under the Transform tab, click the Group By option in the Power Query Editor.

4. Once the Group By window opens, choose the “Basic” option. After that, select the “Product Category” columns. Next, name the column and choose “Sum” as the operation. Finally, select the “Sales Amount” column. Then click OK.

5. You can see the Total Sales value based on the Product category column using Power Query.

This is how to sum group by column in the Power BI.

Power BI Sum Group By using DAX

Suppose you are working as a Data Analyst at an IT company that sells various software and hardware products and provides some services. The company wants to analyze the sales performance of its products, specifically to understand the total sales amount for each product category.

According to this scenario, we have a dataset in Power BI. Please check the screenshot below.

Now follow the below steps:

Navigate to the Table view and click “New table” under the Home tab.

Then, in the formula bar, put the below DAX expression.

SalesSummary = 
GROUPBY(
    'Product Sales',
    'Product Sales'[Category],
    "TotalSalesAmount", SUMX(CURRENTGROUP(), 'Product Sales'[Quantity Sold] * 'Product Sales'[Unit Price])
)

Where:

SalesSummary = This part names the table we create, summarizing sales data by category.
GROUPBY() = This function creates a summary table that groups data by one or more columns. It allows for custom aggregations within each group.
‘Product Sales'[Category] = This specifies the column by which we are grouping the data. In this case, it’s the ‘Category’ column from the ‘Product Sales’ table.
“TotalSalesAmount” = This is the name of the new column we’re creating within each group. It will contain the total sales amount for that category.
SUMX(CURRENTGROUP(), ‘Product Sales'[Quantity Sold] * ‘Product Sales'[Unit Price])= This part defines the calculation for the “TotalSalesAmount” column.
SUMX() = This function performs row-by-row calculations and then summarizes the results.
CURRENTGROUP() = This function returns the table that represents the current group being processed by GROUPBY.
‘Product Sales'[Quantity Sold] * ‘Product Sales'[Unit Price] = This multiplies the quantity sold by the unit price for each row in the current group to get the total sales amount for that row.

Then you can see in the table view a new table created.

This way, you can create a Power BI sum group by using DAX.

Power BI Sum Group By Multiple Columns

Imagine you’re running an e-commerce store and want to understand how sales perform across different regions and product categories. You want to calculate the total order amount for each region and product category combination.

According to this scenario, a SharePoint list named Customer Orders contains the following columns. Check the screenshot below.

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Under the Home tab, click Transform date.

3. In the Power Query Editor, under the Transform tab, click Group By.

4. After opening the Group By window, click on “Advanced options,” select the “Product Category” column, add it as a grouping, and then choose the “Region” column. Enter a new column name, select “Sum” as the operation, and choose “Order Amount” as the column. Finally, click “OK.”

5. Now, in the screenshot below, you can see that the two columns are grouped and display the Total Order Amount.

This is how to sum and group by Multiple columns in Power BI.

Power BI Measure Sum by group

You can’t directly use the “Group By” function within a measure in Power BI because measures are designed to return single values, not tables with multiple rows and columns.

Instead, you typically use a combination of functions like SUMX, FILTER, and ALLEXCEPT to achieve a sum by group.

Example:

You have a table with sales data, and you want to calculate the sum of sales by category; you would create a measure that iterates over each row of the table, filters it based on the category, and then sums up the sales for that category.

You can use below DAX expression:

Total Sales by Category = 
SUMX(
    VALUES('Sales'[Category]),
    CALCULATE(
        SUM('Sales'[SalesAmount]),
        ALLEXCEPT('Sales', 'Sales'[Category])
    )
)

Where:

Total Sales by Category = This part names the measure we’re creating, representing each category’s total sales amount.
SUMX() = This function iterates through a table and calculates a sum for each row.
VALUES(‘Sales'[Category]) = This function returns a single-column table containing all the unique values from the ‘Category’ column in the ‘Sales’ table.
CALCULATE(): This function evaluates an expression in a context modified by filters.
SUM(‘Sales'[SalesAmount]): This calculates the sum of the ‘SalesAmount’ column in the ‘Sales’ table.
ALLEXCEPT(‘Sales’, ‘Sales'[Category]): This removes all filters from the ‘Sales’ table except for the ‘Category’ column.

Power BI DAX Sum Group By with Filter

Imagine you are an analyst at a retail company. Your company has sales data, and you need to calculate the total sales for each product category only for the current month. You also need to include a filter to consider only sales where the amount is greater than 100.

To do this, follow the below steps:

1. Open Power BI Desktop, then under the Home tab, click Enter data.

2. Then the below Create Table dialog box will appear. We use the ‘+‘ symbols to add new columns and rows.

3. We can add data to the table by typing it into the grid.

4. Now, our table is created. Then, specify the table name under the Name field and Click Load.

5. Go to the “Modeling” tab and click on “New Table.” In the formula bar, enter the following DAX expression:

FilteredSales = 
FILTER(
    'Sales Table',
    'Sales Table'[Sale Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) &&
    'Sales Table'[Sale Date] < DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) &&
    'Sales Table'[Sale Amount] > 100
)

Where:

FilteredSales = This part names the table we create, representing the filtered sales data.
FILTER() = This Filter() function returns a table that includes only the rows that meet the specified conditions.
‘Sales Table’ = This is the table we are filtering. It contains the sales data.
‘Sales Table'[Sale Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) = This condition filters the rows to include only those where the ‘Sale Date’ is greater than or equal to the first day of the current month.
‘Sales Table'[Sale Date] < DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) = This condition filters the rows to include only those where the ‘Sale Date’ is less than the first day of the next month.
‘Sales Table'[Sale Amount] > 100 = This condition filters the rows to include only those where the ‘Sale Amount’ is greater than 100.

6. Then, you can see a table with sales greater than 100, all from the current month.

Now we create the GroupedSales table:

7. Go to the “Modeling” tab and click on “New Table.” In the formula bar, enter the following DAX expression:

GroupedSales = 
GROUPBY(
    FilteredSales,
    FilteredSales[Product Category],
    "TotalSales", SUMX(CURRENTGROUP(), FilteredSales[Sale Amount])
)

Where:

GroupedSales = This part names the table or expression we’re creating, which summarizes sales data by product category.
GROUPBY() = This function creates a summary table that groups data by one or more columns. It allows for custom aggregations within each group.
FilteredSales = This specifies the table we’re grouping, which is the previously filtered sales data.
FilteredSales[Product Category] = This specifies the column by which we are grouping the data. In this case, it’s the ‘Product Category’ column from the ‘FilteredSales’ table.
“TotalSales” = This is the name of the new column we’re creating within each group. It will contain the total sales amount for that category.
SUMX(CURRENTGROUP(), FilteredSales[Sale Amount]) = This part defines the calculation for the “TotalSales” column.

8. You can see the Total Sales value based on the Product category column.

This way, you can calculate the Power BI Dax sum group with a filter.

This Power BI tutorial taught us how to sum and display values using the group by function, including its application in Power Query. We also discussed utilizing DAX for summing with group by, summing with group by across multiple columns, measuring sums by group, and applying DAX sum group by with filters.

You may also like the following tutorials:

Bijay Kumar

www.enjoysharepoint.com

]]>

Power BI Slicer Multiple Columns

Tue, 04 Jun 2024 13:48:45 +0000

A Power BI slicer lets you filter data easily. You can use multiple columns to slice and dice your data for clearer insights.

In this tutorial, we will learn how to create a slicer with multiple columns in Power BI and a Power BI slicer with a vertical list of multiple columns.

Additionally, we will discuss the topics below:

Power BI slicer with multiple fields
Power BI slicer search
Power BI slicer multiple columns without hierarchy

Power BI Slicer Multiple Columns

Power BI Slicer Multiple Columns is a feature in Power BI that allows you to filter data based on more than one column simultaneously. Think of it as a tool that helps you narrow down data in your report by selecting criteria from multiple categories.

For example, you can use it to filter sales data by both product type and region at the same time, making your data analysis more flexible and specific.

How to Create Slicer With Multiple Columns in Power BI?

Imagine you are a sales manager for a company, and you need to analyze sales data across different regions and product categories. You want to create a slicer that allows you to filter your sales report based on combinations of regions and product categories.

According to this scenario, we have a SharePoint list named Retail Sales that contains the following columns with various data types:

Columns	Data types
Date	Data and time
Region	Single line of text
Category	Single line of text
Sales	Currency

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Under the Home tab, expand Visual gallery -> Click the Slicer visual.

Here, do not use Silcer (new) because we do not add more than of field in Slicer (new).

3. Then, using the +Add data option, add Category and Region into the Field.

4. You will see that our multiple-column slicer has been created successfully.

This way, you can create a slicer with multiple columns in Power BI.

Power BI Slicer with Multiple Fields

Let’s see how to use the Slicer visual with multiple fields in Power BI.

To do this, I already loaded the below data set in Power BI:

Now follow the below steps:

1. Here, First, we created a Product hierarchy that contains the Product Category, Region, and Product Name columns.

If you don’t know how to create a hierarchy, check out our hierarchy tutorial.

2. Under the Home tab, expand Visual gallery -> Click the Slicer visual. Then, using the +Add data option, add Product hierarchy and Price into the Field.

3. Here, I created a table visual that contains the Date, Product Name, Product Category, Region, and Price columns.

4. Expand the Clothing category in the slicer visual. Then, when I select South, only Clothing products and sales items from the South region will be displayed in the table visually.

5. If you expand further and select the price, the table visual will show only the items with the selected price.

This is how to use the Slicer visual with multiple fields in Power BI.

Power BI Slicer Vertical List Multiple Columns

Now we see how to create a vertical list of multiple columns Power BI Slicer.

Imagine you’re making a slicer with multiple columns containing Product Categories and Product Names. However, you notice that the slicer isn’t displaying as a vertical list with multiple columns.

To do a vertical list of multiple columns, follow the below steps:

1. Select the slicer visual -> click format option -> expand the Slicer settings -> select Vertical list.

2. Then, you can see the Slicer vertical list of multiple columns created.

This way, you can display the Power BI slicer vertical list of multiple columns.

Power BI Slicer Search

This example shows how to find a search option in Power BI Slicer.

Here, I created a Power BI slicer that shows all the Product names.

To add a search box to the slicer, follow the below steps:

1. Select the slicer and click the three dots at the top. Then, choose ‘Search’.

2. Then, you can see a search box added to the slicer.

This way, you can add a search box to the Power BI slicer.

Power BI Slicer Multiple Columns Without Hierarchy

This example shows how to create slicer multiple columns without hierarchy.

Here, we are using the above data set containing the Date, Product Name, Product Category, Region, and Price columns.

Now follow the below steps:

1. Under the Modeling tab, expand the New parameter and click Fields.

2. In the Parameters window, name your fields and select the columns you want for the multiple-column slicer. Then, click ‘Create’.

3. Then you can see it automatically created a slicer.

4. A new table will be created in the data panel. You can use this table to create any visual. Here, I’ve created a column chart and added the product from the new table.

5. When I select a Product Category, the column chart shows the Price by That Category. When I select a Product Name, the column chart shows the Price by That Name.

This way, you can create a Power BI slicer with multiple columns without hierarchy.

In this article, we covered how to create a slicer with multiple columns in Power BI, how to set up a slicer with a vertical list of multiple columns, how to use a slicer with multiple fields, how to implement a slicer search feature, and how to configure a slicer with multiple columns without hierarchy.

You may also like the following tutorials:

Bijay Kumar

www.enjoysharepoint.com

]]>