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
Anonymous
Not applicable

FORMAT INSTRUCTION WITHIN DYNAMIC TITLE MEASURE NOT APPLYING

I have a measure which shows which months have been selected from a slicer

The below syntax works, but ignores my FORMAT argument, therefore if I select Jan and Feb 2021 from my slicer (called Reporting Period), the FILTER measure displays "REPORTING MONTHS: 01/01/2021 | 01/02/2021"

FILTER = IF(ISFILTERED('Dim_Date'[REPORTING PERIOD]), "REPORTING MONTHS: "& UPPER(FORMAT(CONCATENATEX(VALUES('Dim_Date'[REPORTING PERIOD]),'Dim_Date'[REPORTING PERIOD]," | "),"MMM YYYY")))

What I want to appear is "REPORTING MONTHS: JAN 2021 | FEB 2021"

Is this possible within this type of measure?

 

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

@Anonymous - The FORMAT is outside of the CONCATENATEX so it will not be recognised as DATE.  You might be able to try something like:

FILTER = 
VAR _RepMths = 
DISTINCT(
SELECTCOLUMNS(
'Dim_Date' ,
"Formatted Reporting Months" ,
FORMAT( 'Dim_Date'[REPORTING PERIOD], "MMM YYYY")
)
)
RETURN
IF(
ISFILTERED('Dim_Date'[REPORTING PERIOD]),
"REPORTING MONTHS: "& UPPER( CONCATENATEX( _RepMths , [Formatted Reporting Months] ," | ") ),
BLANK()
)

View solution in original post

2 REPLIES 2
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

@Anonymous - The FORMAT is outside of the CONCATENATEX so it will not be recognised as DATE.  You might be able to try something like:

FILTER = 
VAR _RepMths = 
DISTINCT(
SELECTCOLUMNS(
'Dim_Date' ,
"Formatted Reporting Months" ,
FORMAT( 'Dim_Date'[REPORTING PERIOD], "MMM YYYY")
)
)
RETURN
IF(
ISFILTERED('Dim_Date'[REPORTING PERIOD]),
"REPORTING MONTHS: "& UPPER( CONCATENATEX( _RepMths , [Formatted Reporting Months] ," | ") ),
BLANK()
)
Anonymous
Not applicable

Thanks you clever little sausage! It worked perfectly! Didn't occur to be to create a variable. Great 😄

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.