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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors