Forum Discussion

prabinnepak's avatar
prabinnepak
Regular Visitor
8 years ago

Aggregation on top of summarize function

 

Hi All,
I have a dataset where i just need to calculate the max and min of a particular service data for a certain member and a claim 
and need to calculate an aggregate on top
 
Sample data 
 

 

CLAIM_HCC_IDMEMBER_HCC_IDSERVICE__DATE
2017649741006200-021/10/2017
2017649748002362-011/13/2017
2017660748006897-011/23/2017
2017653748012926-051/13/2017

Here there might be multiple service dates for a single member.
So for each member and the claim, i just wanted to know the minimum and the maximum service dates respectively.
 
 
 
Formulas :
MinDate = SUMX(SUMMARIZE('Reports vw_Health_Services_utilization','Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],"MIN",MIN('Reports vw_Health_Services_utilization'[MIN_SERVICE_START_DATE].[Date])),[MIN])

MaxDate = SUMX(SUMMARIZE('Reports vw_Health_Services_utilization','Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],"Max",MAX('Reports vw_Health_Services_utilization'[MAX_SERVICE_END_DATE].[Date])),[MAX])

After this i am calculating the difference between both this dates and retrieving the days among this dates

A1= DATEDIFF([MinDate],[MaxDate],DAY)
I need to analyze this monthly 
 
On top of this i wanted an aggregation i.e Sum(A1) but i am getting errors "An invalid numeric representation of a date value was encountered "
 
Can anyone please provide me some feedback 
Prabin Nepak
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi prabinnepak,

     

    According to your formula, I'm not so sure why you try to get sum of date value. Can you explain more about this?


    In addition, if you want to get the grouped min/max date and diff between them, you can try to use below formula to create new table with above data.

    edsummarized =
ADDCOLUMNS (
    SUMMARIZE (
        'Reports vw_Health_Services_utilization',
        'Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],
        'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],
        "MIN", MIN ( 'Reports vw_Health_Services_utilization'[MIN_SERVICE_START_DATE].[Date] ),
        "MAX", MAX ( 'Reports vw_Health_Services_utilization'[MAX_SERVICE_END_DATE].[Date] )
    ),
    "Diff", DATEDIFF ( [MIN], [MAX], DAY )
)

     

    Regards,

    Xiaoxin Sheng

    • prabinnepak's avatar
      prabinnepak
      Regular Visitor
      8 years ago

      Hi

      Thanks for your feedback

      After executing this formula I am getting the following error 

       

      "The expression refers to multiple columns.Multiple columns cannot be converted to a scalar value "

       

      Thanks and Regards

      Prabin Nepak  

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi prabinnepak,

         

        >>"The expression refers to multiple columns.Multiple columns cannot be converted to a scalar value "

        Above formula is calculated table formula, you can't use this formula in calculated column/measure.

         

        Regards,

        Xiaoxin Sheng