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
ryan_mayu
Super User
Super User

URGENT! How to filter data in different table?

Hi all,

 

I have three tables: rawdata, date and type. In the rawdata, I added a new column whose name is "New Type". Then I linked the "New Type" column to the "type" column in type table.

 

I want to calculate the last month's amount and the last second month's amount. I know we can use dateadd formula, but that formula does not work when you choose several month and display data as a card.

 

Below is the formula I used:

 

 
 
test =
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL
 
Then I can use New Type column to filter data and drag that column to a table view. However, when I use type column to filter, the data is incoorect. 
 
Can anyone help me on that?
 
 
 
Date table:
date table.JPG
 
Rawdata table(New Type column was created in PBI,   New Type = SWITCH('Rawdata'[Type],"AAA","New","Old")
rawdata table(new type was added in PBI).JPG
 
Type table
type table.JPG
 
I got the wrong data when I use type column to filter, (test column should show last month's amount)
 
result with wrong data.JPGThanks a lot




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION

hi, @ryan_mayu

Please try this formula,

test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

 

Result:

9.JPG

 

Best Regards,

Lin

 

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

View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

Can anyone help me on this? It's really urgent. Thanks in advance





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




hi, @ryan_mayu

Please try this formula,

test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

 

Result:

9.JPG

 

Best Regards,

Lin

 

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

Hi Lin,

 

It works. Many thanks for your help. Really apprecated that.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

Smiley Happy





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.