Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.