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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NV89
Frequent Visitor

How to filter content of one column in the context of combination of other columns including date

Hi,

I am very new to PowerBI and this is 1st time I am posting a question so, please pardon if something is incorrect. Here is my problem statement.

I have following table with date (random), Store, Person, Product & Sales columns. I want to create a measure which works in context of Date, Store & Person in combine and generates a resulting Output column as shown below. Output column is based on the filter of Product column based on another Table which contains list of products to use for filter. 

 

Table to filter:

Cereal
Tissues
Detergents

 

Input Table (with Output column as expected resulting column)

 

DateStorePersonProductSalesOutput 
1/1/2022Store1MikeCereal1010<- Since Mike from Store1 sold an item from a given table (i.e. Cereal), output is equal to Sales column for that product
1/1/2022Store1MikeCheeses2
1/1/2022Store1MikeCrackers1
1/1/2022Store1JohnTissues55<- Since John from Store1 sold an item from a given table (i.e. Tissues), output is equal to Sales column for that product
1/1/2022Store1JohnCheeses15
1/1/2022Store2RodJuices20<- Since Rod from Store2 didn't sell any items from a given table and we don't have any records of him selling any item from a given table from any previous dates, resulting output is 0
1/5/2022Store1MikeJuices510<- Since Mike from Store1 didn't sell any item from a given table, carry forward the sales of an item that he sold from a table on previous date i.e. he sold 10 Cereal on 1/1/2022
1/8/2022Store1MikeDetergents2020<- Since Mike from Store1 sold one of the item from a given table, the resulting output is 20
1/8/2022Store2JohnTowels100<- Here, John is a different person who works in Store2 and since he didn't sell any item from a given table on current date or any of the previous dates, resulting output is 0
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @NV89 

 

You can try the following methods.

Column:

Column = 
IF([Product] in VALUES('Filter'[Filter]),[Sales])

vzhangti_0-1666062870620.png

Output = 
Var _lastdate=
CALCULATE(MIN('Table'[Date]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Column]<>BLANK()))
Var _InFliter=
CALCULATE(MAX('Table'[Column]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Date]=EARLIER('Table'[Date])))
Var _lastsales=
CALCULATE(MAX('Table'[Sales]),FILTER('Table',[Date]=_lastdate
                               &&[Person]=EARLIER('Table'[Person])&&[Product] in VALUES('Filter'[Filter])))
return
IF(_InFliter=BLANK(),0,IF([Column]<>BLANK(),[Column],IF([Date]<>_lastdate,_lastsales)))

vzhangti_1-1666063061896.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @NV89 

 

You can try the following methods.

Column:

Column = 
IF([Product] in VALUES('Filter'[Filter]),[Sales])

vzhangti_0-1666062870620.png

Output = 
Var _lastdate=
CALCULATE(MIN('Table'[Date]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Column]<>BLANK()))
Var _InFliter=
CALCULATE(MAX('Table'[Column]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Date]=EARLIER('Table'[Date])))
Var _lastsales=
CALCULATE(MAX('Table'[Sales]),FILTER('Table',[Date]=_lastdate
                               &&[Person]=EARLIER('Table'[Person])&&[Product] in VALUES('Filter'[Filter])))
return
IF(_InFliter=BLANK(),0,IF([Column]<>BLANK(),[Column],IF([Date]<>_lastdate,_lastsales)))

vzhangti_1-1666063061896.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

lukiz84
Memorable Member
Memorable Member

Hi,

 

just create a measure 

Output = SUM('Input Table'[Sales])

 

and connect your Product table (1:n) to your Input Table (field Product). Add a slicer from the product table and voilá 🙂

 

(You should also create a seperate date table and connect the date field to the date field of the Input table to be able to use time intelligence functions)

 

br

NV89
Frequent Visitor

This doesn't work. My problem statement is complex than what you have interpreted and replied. 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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