Showing all data without filter

[jairodm]

Greetings from a Power BI beginner

I have the following relationship structure:

The scenario would be to list each unit with its respective employees and a column with the sum of purchases. If a date was selected on which a given employee had no purchase data, the employee would continue to be listed, but with the purchase sum column reset to zero.

I added a segmenter with the date field and I am displaying the unit name data in a matrix and for each unit the employees related to it.
I created the measure with the following formula:
Sum_Purchases = SUM(Purchases[Value])+0
It turns out that the matrix correctly shows each unit with its respective employees, but when adding the measure in the matrix values ​​field, each unit starts showing the list of all employees.
I've already tried variations using countrows and isblank, but the listing of all employees is repeated.

Sum_Purchases =
var aux2=SUM(Purchases[Value])
return

if(ISBLANK(aux2), 0,aux2)

Thank you in advance for your attention.

[123abc]

To achieve the desired result in Power BI where you want to display each unit with its respective employees and a column with the sum of purchases, with employees listed even if they have no purchase data, you can use a combination of DAX measures and relationships. It seems like you're on the right track with your measures, but let's refine the approach.

Assuming you have the following tables in your Power BI model:

1. Units table (with columns: Unit ID and Unit Name)
2. Employees table (with columns: Employee ID, Employee Name, and Unit ID)
3. Purchases table (with columns: Employee ID, Purchase Date, and Purchase Value)

Here's a step-by-step guide on how to achieve the desired result:

1. Create a relationship between the Units table and the Employees table based on the Unit ID field. This should be a one-to-many relationship where each unit can have multiple employees.

2. Create a relationship between the Employees table and the Purchases table based on the Employee ID field. This should also be a one-to-many relationship, as each employee can have multiple purchases.

3. Create a measure to calculate the sum of purchases for each employee. You can use the following DAX formula for this measure:

Sum_Purchases = SUM(Purchases[Purchase Value])

1. Create a matrix visual in your report. Place the following fields in the matrix:

   • Rows: Units[Name]
   • Values: Employees[Name] (from the Employees table)
   • Values: Sum_Purchases (the measure you created in step 3)

2. Now, to ensure that all employees are listed, even if they have no purchase data for the selected date, you can use a DAX measure to filter the employees based on the selected date. Create a measure like this:

Filtered_Sum_Purchases =
VAR SelectedDate = MAX('Date Segmenter'[Date])
RETURN
CALCULATE(
[Sum_Purchases],
FILTER(
ALL(Employees),
NOT ISBLANK([Sum_Purchases]) || ISINSCOPE(Employees[Name])
),
Purchases[Purchase Date] = SelectedDate
)

This measure calculates the sum of purchases for each employee for the selected date. It uses the FILTER function to make sure that all employees are included in the result, even if they have no purchases for the selected date.

1. Replace the "Sum_Purchases" measure in your matrix with the "Filtered_Sum_Purchases" measure.

Now, your matrix should display each unit with its respective employees, and the "Filtered_Sum_Purchases" measure will show the sum of purchases for each employee for the selected date, with employees listed even if they have no purchase data.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[123abc]

To achieve the desired result in Power BI where you want to display each unit with its respective employees and a column with the sum of purchases, with employees listed even if they have no purchase data, you can use a combination of DAX measures and relationships. It seems like you're on the right track with your measures, but let's refine the approach.

Assuming you have the following tables in your Power BI model:

1. Units table (with columns: Unit ID and Unit Name)
2. Employees table (with columns: Employee ID, Employee Name, and Unit ID)
3. Purchases table (with columns: Employee ID, Purchase Date, and Purchase Value)

Here's a step-by-step guide on how to achieve the desired result:

1. Create a relationship between the Units table and the Employees table based on the Unit ID field. This should be a one-to-many relationship where each unit can have multiple employees.

2. Create a relationship between the Employees table and the Purchases table based on the Employee ID field. This should also be a one-to-many relationship, as each employee can have multiple purchases.

3. Create a measure to calculate the sum of purchases for each employee. You can use the following DAX formula for this measure:

Sum_Purchases = SUM(Purchases[Purchase Value])

1. Create a matrix visual in your report. Place the following fields in the matrix:

   • Rows: Units[Name]
   • Values: Employees[Name] (from the Employees table)
   • Values: Sum_Purchases (the measure you created in step 3)

2. Now, to ensure that all employees are listed, even if they have no purchase data for the selected date, you can use a DAX measure to filter the employees based on the selected date. Create a measure like this:

Filtered_Sum_Purchases =
VAR SelectedDate = MAX('Date Segmenter'[Date])
RETURN
CALCULATE(
[Sum_Purchases],
FILTER(
ALL(Employees),
NOT ISBLANK([Sum_Purchases]) || ISINSCOPE(Employees[Name])
),
Purchases[Purchase Date] = SelectedDate
)

This measure calculates the sum of purchases for each employee for the selected date. It uses the FILTER function to make sure that all employees are included in the result, even if they have no purchases for the selected date.

1. Replace the "Sum_Purchases" measure in your matrix with the "Filtered_Sum_Purchases" measure.

Now, your matrix should display each unit with its respective employees, and the "Filtered_Sum_Purchases" measure will show the sum of purchases for each employee for the selected date, with employees listed even if they have no purchase data.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.