Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a simple PowerBI that gives me a userlist with their summed of sales ($).
Underlying data is:
customerid, date, sum(sales)
from 1st january - 1 october
I would like to be able to set a slicer, that the viewer can filter for customers e.g. that spend > 200$.
thing is that the slicer applies to the underlying data, so it filters out single rows < 200$ and does not apply to the summed up values shown in the table.
Anybody can explain me how I can make a slicer that applies to the summed up values?
Hi @horror,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
What you are looking for is the disconnected table trick. I have an upcoming blog post on this in the Community Blog (under the "more" dropdown above). Basically, create a table that is unrelated to anything with your slicer values. Here is an abbreviated version of the article:
Given the set of these core tables:
Employees
Employee |
Greg |
Bill |
Joe |
David |
Jimbo |
Training
Employee | Training | Date |
Greg | Training 1 | 9/19/2017 |
Bill | Training 1 | 9/19/2017 |
Joe | Training 1 | 9/19/2017 |
David | Training 1 | 9/19/2017 |
Jimbo | Training 1 | 9/19/2017 |
Greg | Training 2 | 8/19/2017 |
Bill | Training 2 | 8/19/2017 |
David | Training 2 | 8/19/2017 |
Greg | Training 3 | 7/19/2017 |
Bill | Training 3 | 7/19/2017 |
Joe | Training 3 | 7/19/2017 |
David | Training 3 | 7/19/2017 |
Hours
Employee | Week | Hours |
Greg | 9/17/2017 | 40 |
Greg | 9/10/2017 | 40 |
Greg | 9/3/2017 | 40 |
Greg | 8/27/2017 | 40 |
Greg | 8/20/2017 | 40 |
Greg | 8/13/2017 | 40 |
Greg | 8/8/2017 | 40 |
Greg | 8/1/2017 | 40 |
Joe | 9/17/2017 | 24 |
Joe | 9/10/2017 | 24 |
Joe | 9/3/2017 | 24 |
Joe | 8/27/2017 | 24 |
Joe | 8/20/2017 | 24 |
Joe | 8/13/2017 | 24 |
Joe | 8/8/2017 | 24 |
Joe | 8/1/2017 | 24 |
The Employees table is simply a unique list of Employee ID's. The Training table has a row for every training event that an employee attended. Finally, the Hours table lists the week ending date when an employee worked. Employees are expected to attend training events when they are working and are not expected to attend training events when they are not working. The Hours table has been abbreviated.
The relationships between the tables are as follows. All relationships are between the Employee columns of the tables.
Employees 1<>* Hours
Employees 1>* Training
Measures:
Attendance = IF(ISBLANK(MAX([Date])),"Not Attended","Attended")
Attended = IF(ISBLANK(MAX([Date])),BLANK(),"Attended") NotAttended = IF(ISBLANK(MAX([Date])),"Not Attended",BLANK())
Measures to Show = IF( // This first IF condition forces the measure to evaluate in the context of the visual HASONEVALUE(Employees[Employee]), // This next condition avoids getting the error that the visual cannot be displayed IF(HASONEVALUE(Attendance[Attendance]), // This switch statement uses the values from our Attendance slicer to determine which measure to display SWITCH( VALUES(Attendance[Attendance]), "Attended",[Attended], "Not Attended",[NotAttended] ), // If the Attendance slicer has not been selected, just display the date of the training MAX([Date]) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |