Forum Discussion

JasonZentner's avatar
JasonZentner
Frequent Visitor
6 years ago

Top N by other Measure

I am attempting to get the last 12 eligible weeks based on this table. This will need to be a measure, as it will apply to multiple employees. 

 

The Answer I expect is 473.

 

Dataset

The ElWeeks is a measure that takes several other measures into account before determining if the week is eligible.
This will be scoped across 1,000+ employees, with 10M+rows of data, so a measure is needed, not a calculated column if I can help it. 

DC startdate = DISTINCTCOUNT(Table[Start Date])
DC Weeks = DISTINCTCOUNT(Table[Week])
AverageDC = ROUND([DC startdate]/[DC Weeks],0)
DC ElWeeks = CALCULATE([AverageDC],all(Table[Week]))
ElWeeks = if([DC startdate]>=[DC ElWeeks],1,0)
Total hours per week = if([ElWeeks]>0,sum('Table'[Hours])*[ElWeeks],0)

 

 

 

When I try to create a new measure(s) of 

Rank = IF([ElWeeks]=0,BLANK(),RANKX(Table,Table[Week],,ASC,Dense))
Rank = IF('Table'[ElWeeks]=0,BLANK(),RANKX('Table','Table'[Week],,ASC,Dense))
 
I get an error of: "A single value for column 'Week' in Table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Need Help
Show and Tell

6 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JasonZentner ,

    Does this meet your requirements? This is my PBIX file.

     

    Best Regards,

    Icey

     

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

    • JasonZentner's avatar
      JasonZentner
      Frequent Visitor
      6 years ago

      Hi Icey ,

       

      Thank you for getting back to me.  The short answer is no.  Now the longer one:

       

      The ElWeeks is a measure that takes several other measures into account before determining if the week is eligible. This will be scoped across 1,000+ employees, with 10M+rows of data, so a measure is needed, not a calculated column if I can help it. 

       

       

      DC startdate = DISTINCTCOUNT(Table[Start Date])
      DC Weeks = DISTINCTCOUNT(Table[Week])
      AverageDC = ROUND([DC startdate]/[DC Weeks],0)
      DC ElWeeks = CALCULATE([AverageDC],all(Table[Week]))
      ElWeeks = if([DC startdate]>=[DC ElWeeks],1,0)
      Total hours per week = if([ElWeeks]>0,sum('Table'[Hours])*[ElWeeks],0)

       

      When I try to create a new measure(s) of 

      Rank = IF([ElWeeks]=0,BLANK(),RANKX(Table,Table[Week],,ASC,Dense))
      Rank = IF('Table'[ElWeeks]=0,BLANK(),RANKX('Table','Table'[Week],,ASC,Dense))
       


      I get an error of: "A single value for column 'Week' in Table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

    • JasonZentner's avatar
      JasonZentner
      Frequent Visitor
      6 years ago

      Hi Icey ,

       

      Thank you for getting back to me.  The short answer is no.  Now the longer one:

       

      The ElWeeks is a measure that takes several other measures into account before determining if the week is eligible.

       

      This will be scoped across 1,000+ employees, with 10M+rows of data, so a measure is needed, not a calculated column if I can help it. 

       

       

      DC startdate = DISTINCTCOUNT(Table[Start Date])
      DC Weeks = DISTINCTCOUNT(Table[Week])
      AverageDC = ROUND([DC startdate]/[DC Weeks],0)
      DC ElWeeks = CALCULATE([AverageDC],all(Table[Week]))
      ElWeeks = if([DC startdate]>=[DC ElWeeks],1,0)
      Total hours per week = if([ElWeeks]>0,sum('Table'[Hours])*[ElWeeks],0)

       

      When I try to create a new measure(s) of 

      Rank = IF([ElWeeks]=0,BLANK(),RANKX(Table,Table[Week],,ASC,Dense))
      Rank = IF('Table'[ElWeeks]=0,BLANK(),RANKX('Table','Table'[Week],,ASC,Dense))
       
      I get an error of: "A single value for column 'Week' in Table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi JasonZentner ,

        I modified it, and you can create your measures like so:

        Week Measure = MAX ( 'Table'[Week] )
        Rank measure =
IF (
    MAX ( 'Table'[EIWeeks] ) = 0,
    BLANK (),
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[EIWeeks] <> 0 ),
        [Week Measure],
        ,
        DESC,
        DENSE
    )
)
        Total last 12 weeks =
CALCULATE (
    SUM ( 'Table'[Total hours per week] ),
    FILTER ( 'Table', [Rank measure] <= 12 )
)

        PBIX file attached.

         

        Best Regards,
        Icey

         

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

    • JasonZentner's avatar
      JasonZentner
      Frequent Visitor
      6 years ago

      Hi Icey,

       

      It's not, sorry.  The issue is the calling of one measure by another measure. In your example, the ranking is based on the IsValid field being a column, but this is actually a measure. 

       

      We're going back to the drawing board to see if we can restructure the data before it comes into PBI. 

       

      Thank you for your assistance. 

      Jason