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

Not able to aggregate data into unique categories

I am looking to aggregate all values greater than 2 months under "3+ Months Ago" however when I put the result in a visual the value "3+ Months Ago" appears multiple times, one per each value of the Var MthSince greater than 2.
 
Later I realized this behavior is not limited to "3+ Months Ago" but all categories.
 
Need help to fix it, thank you in advance.
 
Months Since Update =
VAR MthSince =
    DATEDIFF ( 'T360-Matters'[Last Update Date], TODAY (), MONTH )
RETURN
    SWITCH (
        MthSince,
        0, "This Month",
        1, "Last Month",
        2, "2 Months Ago",
        "3+ Months Ago"
    )
 
Here is result in table visual, 
image.png
 
1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

Hi @kh4hk,

 

Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue?

 

It's better to just share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploadingSmiley Happy

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Microsoft
Microsoft

Hi @kh4hk,

 

Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue?

 

It's better to just share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploadingSmiley Happy

 

Regards

I was trying to annonymize the data to share it with you and solved the issue in doing so.

 

The sorter formula was not aligned with Month Since Update column I shared earlier.

 

(Before update) Month Since Update Sort = DATEDIFF ( 'T360-Matters'[Last Update Date], TODAY (), MONTH )

(After update) Month Since Update Sort = Var MthSince = DATEDIFF('T360-Matters'[Last Update Date], TODAY(), MONTH) Return IF(MthSince=0,0,IF(MthSince=1,1, IF(MthSince=2, 2, 3)))

 

Thank you so much for guiding me in right direction, appreciate your help.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.