Forum Discussion
Summary and Ranking
This code works for me as is but I would like to narrow it down based on the date. When I try to add another condition to the filter which is Table_A[Call Date] IN DATESBETWEEN "date1" and date1" I get the error "single value for date can't be determined. I feel like this has something to do with row context. How would I add that condition as well to my existing code?
DEFINE
VAR REPS =
FILTER (
ADDCOLUMNS (
SUMMARIZE (
FILTER (
'Table_A',
'Table_A'[Name] <> "N/A"
),
'Table_A'[Name]
),
"@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
"@rank",
RANKX (
ALL ( 'Table_A'[Name] ),
CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
,
DESC,
DENSE
)
), //addcolumns
[@rank] = 1 || [@rank] = 2 )
EVALUATE
REPS
4 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi stribor45 ,
Please try:
If you need a date range:
DEFINE VAR _date1=DATE(2023,1,1) //You can change this date variable to suit your needs VAR _date2=DATE(2023,1,3) ////You can change this date variable to suit your needs VAR REPS = FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Table_A', 'Table_A'[Name] <> "N/A" && 'Table_A'[Call Date] IN DATESBETWEEN('Table_A'[Call Date],_date1, _date2) ), 'Table_A'[Name] ), "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ), "@rank", RANKX ( ALL ( 'Table_A'[Name] ), CALCULATE ( COUNT ( 'Table_A'[ID] ) ), , DESC, DENSE ) ), //addcolumns [@rank] = 1 || [@rank] = 2 ) EVALUATE REPSIf you need to equal a single date:
DEFINE VAR _date1=DATE(2023,1,1) //You can change this date variable to suit your needs VAR REPS = FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Table_A', 'Table_A'[Name] <> "N/A" && 'Table_A'[Call Date] =_date1 ), 'Table_A'[Name] ), "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ), "@rank", RANKX ( ALL ( 'Table_A'[Name] ), CALCULATE ( COUNT ( 'Table_A'[ID] ) ), , DESC, DENSE ) ), //addcolumns [@rank] = 1 || [@rank] = 2 ) EVALUATE REPSBest Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works but I noticed that is N/A somehow has rank 1 it will be excluded from the resulting table
Hi stribor45 ,
Try it.
DEFINE VAR _date1=DATE(2023,1,1) //You can change this date variable to suit your needs VAR _date2=DATE(2023,1,3) ////You can change this date variable to suit your needs VAR REPS = FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Table_A', 'Table_A'[Call Date] IN DATESBETWEEN('Table_A'[Call Date],_date1, _date2) ), 'Table_A'[Name] ), "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ), "@rank", RANKX ( ALL ( 'Table_A'[Name] ), CALCULATE ( COUNT ( 'Table_A'[ID] ) ), , DESC, DENSE ) ), //addcolumns [@rank] = 1 || [@rank] = 2 ) EVALUATE REPSOr:
DEFINE VAR _date1=DATE(2023,1,1) //You can change this date variable to suit your needs VAR REPS = FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Table_A', 'Table_A'[Call Date] =_date1 ), 'Table_A'[Name] ), "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ), "@rank", RANKX ( ALL ( 'Table_A'[Name] ), CALCULATE ( COUNT ( 'Table_A'[ID] ) ), , DESC, DENSE ) ), //addcolumns [@rank] = 1 || [@rank] = 2 ) EVALUATE REPSIf I've misunderstood you, please accept the current post answer as a solution and create a new post detailing your new questions and expected results, thanks in advance.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
