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
rachaelwalker
Resolver III
Resolver III

Paginated Report SEARCH text that contains Parameter

I have a paginated report that has a relative date parameter: Previous Month, Previous Quarter, YTD, FYTD. 

 

rachaelwalker_0-1699044960374.png

rachaelwalker_2-1699045322613.png

 

All of them are filtering correctly except YTD. YTD is also returning FYTD data. Is there a way for it to search and only return those that match the exact parameter? I will include my current query below

 

The [Date_Period_Combine] field is a merged column of each date period it belongs too. 

 

rachaelwalker_0-1699046122845.png

 

I added the parameter within the query designer using the CONTAINS operator and this is the code that is added to query

rachaelwalker_3-1699045634689.png

 

 

DEFINE VAR vProductSalesDatePeriodCombine1 = IF(PATHLENGTH(@ProductSalesDatePeriodCombine) = 1, IF(@ProductSalesDatePeriodCombine <> "", @ProductSalesDatePeriodCombine, BLANK()), IF(PATHITEM(@ProductSalesDatePeriodCombine, 2) <> "", PATHITEM(@ProductSalesDatePeriodCombine, 2), BLANK()))
VAR vProductSalesDatePeriodCombine1ALL = PATHLENGTH(@ProductSalesDatePeriodCombine) > 1 && PATHITEM(@ProductSalesDatePeriodCombine, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Product Sales'[Manufacturer], 'Product Sales'[Sold By Group], 'Product Sales'[Sold By], 'Product Sales'[Account Manager], 'Product Sales'[Company], 'Product Sales'[RoomReady Cost], 'Product Sales'[Partner Cost], 'Product Sales'[Total Cost], 'Product Sales'[Month Year], 'Product Sales'[Year Quarter], 'Product Sales'[Date Period Combine], 'Product Sales'[MIN Date Entered], 'Product Sales'[Max Date Entered], RSCustomDaxFilter(@ProductSalesManufacturer,EqualToCondition,[Product Sales].[Manufacturer],String), FILTER(VALUES('Product Sales'[Date Period Combine]), (vProductSalesDatePeriodCombine1ALL || IF(vProductSalesDatePeriodCombine1 <> "", FIND(vProductSalesDatePeriodCombine1, 'Product Sales'[Date Period Combine], 1, 0) > 0, TRUE))))

 

Within the last FILTER expression, can I tell it to search for the exact matching so FYTD is not returned with YTD

 

FILTER(VALUES('Product Sales'[Date Period Combine]), (vProductSalesDatePeriodCombine1ALL || IF(vProductSalesDatePeriodCombine1 <> "", SEARCH(vProductSalesDatePeriodCombine1, 'Product Sales'[Date Period Combine], 1, 0) > 0, TRUE)))).

 

I am trying to avoid changing the text YTD to "Year to Date" to make this work. I appreciate your help and time!

0 REPLIES 0

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors
Top Kudoed Authors