cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prabinnepak
Regular Visitor

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
3 REPLIES 3
Community Support
Community Support

Re: Aggregation on top of summarize function

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
prabinnepak
Regular Visitor

Re: Aggregation on top of summarize function

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  

Community Support
Community Support

Re: Aggregation on top of summarize function

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications