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.
I am hoping to create table that grabs only the salespeople that have made at least one sale each month. The table I want would only show Bob since he has sales each month and Nancy failed to record one for the month of April. Please let me know if you can be of assitance. Thanks.
Month | Salesperson | quantity |
april | bob | 10 |
april | nancy | 0 |
may | bob | 29 |
may | nancy | 12 |
june | bob | 24 |
june | nancy | 15 |
Solved! Go to Solution.
I think something along these lines should work:
NewTable =
VAR MonthCount = DISTINCTCOUNT ( Sales[Month] )
RETURN
FILTER (
VALUES ( Sales[Salesperson] ),
CALCULATE ( DISTINCTCOUNT ( Sales[Month] ), Sales[Quantity] > 0 ) = MonthCount
)
Hi @bw70316,
You can create a measure formula as below and use it on the 'visual level filter' to filter not matched records:
Isfulltime =
VAR _cMonth =
CALCULATE (
COUNTA ( 'Table'[Month] ),
FILTER ( ALLSELECTED ( 'Table' ), [quantity] > 0 ),
VALUES ( 'Table'[Salesperson] )
)
VAR total =
CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Month] ) ), ALLSELECTED ( 'Table' ) )
RETURN
IF ( total = _cMonth, 1, 0 )
Applying a measure filter in Power BI - SQLBI
Regards,
Xiaoxin Sheng
Hi @bw70316,
You can create a measure formula as below and use it on the 'visual level filter' to filter not matched records:
Isfulltime =
VAR _cMonth =
CALCULATE (
COUNTA ( 'Table'[Month] ),
FILTER ( ALLSELECTED ( 'Table' ), [quantity] > 0 ),
VALUES ( 'Table'[Salesperson] )
)
VAR total =
CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Month] ) ), ALLSELECTED ( 'Table' ) )
RETURN
IF ( total = _cMonth, 1, 0 )
Applying a measure filter in Power BI - SQLBI
Regards,
Xiaoxin Sheng
I think something along these lines should work:
NewTable =
VAR MonthCount = DISTINCTCOUNT ( Sales[Month] )
RETURN
FILTER (
VALUES ( Sales[Salesperson] ),
CALCULATE ( DISTINCTCOUNT ( Sales[Month] ), Sales[Quantity] > 0 ) = MonthCount
)
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |