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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
wojbal
Frequent Visitor

Present values based on two filters selected from the different slicer

Hello,

 

I want to see the Price and Reviews of items based on the selected date. 

When I'm using only one data slicer the values are present, however, when I'm selecting two different dates on two slicers the values are not showing correctly. 

 

I've created those measurements:

 

D-Price (EndDate) = CALCULATE(AVERAGE(MergedTables[Price]),FILTER('Sorted Dates', SELECTEDVALUE('Sorted Dates'[EndDate])))

D-Price (StartDate) = CALCULATE(AVERAGE(MergedTables[Price]),FILTER('Sorted Dates', SELECTEDVALUE('Sorted Dates'[StartDate])))

D-Reviews (EndDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER('EndDates', SELECTEDVALUE('Sorted Dates'[EndDate])))), 0, CALCULATE(MAX(MergedTables[AmountReviews]),FILTER('Sorted Dates', SELECTEDVALUE('EndDates'[EndDate]))))

D-Reviews (StartDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER('StartDates', SELECTEDVALUE('Sorted Dates'[StartDate])))), 0, CALCULATE(MAX(MergedTables[AmountReviews]),FILTER('Sorted Dates', SELECTEDVALUE('StartDates'[StartDate]))))

 

 

Where the table SortedDates is:

 

Sorted Dates = 
ADDCOLUMNS(
DISTINCT( 
    MergedTables[Date]),
    "DateSort", - DATEDIFF ( TODAY (), MergedTables[Date] , DAY ), "StartDate", MergedTables[Date], "EndDate", MergedTables[Date]
)

 

 

And the Merged Tables look similar to: 

ActivityPriceReviewsDate
ABC101014/08/2022
ABC111015/08/2022
ABC101116/08/2022
ABC151217/08/2022
DEF2415014/08/2022
DEF2315015/08/2022
DEF2015016/08/2022
DEF1215117/08/2022
AAA9020014/08/2022
AAA34525015/08/2022
AAA9030016/08/2022
AAA9035017/08/2022

 

The idea is to show values based on selected Start and End dates. Since sometimes I would like to compare today to yesterday and to last month/year value etc.

 

 

1 ACCEPTED SOLUTION
wojbal
Frequent Visitor

I probably found the solution for my own issue. So I've changed the measure to the below one. Also very important to delete the connection between Merged Tables and tables which are in the Slicers.

 

 

D-Price (EndDate) = CALCULATE(AVERAGE(MergedTables[Price]), FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date])))

D-Price (StartDate) = CALCULATE(AVERAGE(MergedTables[Price]), FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date])))

D-Reviews (EndDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date])))),
0,
CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date]))))

D-Reviews (StartDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date])))),
0,
CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date]))))

 

 

 

 

View solution in original post

1 REPLY 1
wojbal
Frequent Visitor

I probably found the solution for my own issue. So I've changed the measure to the below one. Also very important to delete the connection between Merged Tables and tables which are in the Slicers.

 

 

D-Price (EndDate) = CALCULATE(AVERAGE(MergedTables[Price]), FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date])))

D-Price (StartDate) = CALCULATE(AVERAGE(MergedTables[Price]), FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date])))

D-Reviews (EndDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date])))),
0,
CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(EndDates[Date]))))

D-Reviews (StartDate) = IF(ISBLANK(CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date])))),
0,
CALCULATE(MAX(MergedTables[AmountReviews]),FILTER(MergedTables, MergedTables[Date] = SELECTEDVALUE(StartDates[Date]))))

 

 

 

 

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors