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
andrmh
Helper II
Helper II

Chart Titles that adjust to the drill down layer

Hi All,

 

I have a chart that displays Invoicing by Month.

InvoicingByMonth.png

 

At the upper most level, the title is fine. Invoicing by Month. The report is designed to be for Financial YTD only, so will only ever show a max of 12 columns.

 

However the report has a drill down, for each month that breaks the invoicing out to day of month.

InvoicingByMonthByDay.png

 

This shows the title "Invoicing by Month by Day of Month" which is not what im after.

If I have drilled down into July, I'd like the report title to show "Invoicing for July 2016 by day of month", so the person running the report has some context for what they are looking at.

 

Its rather difficult to explain the usefulness of a report to a sales exec of when they are trying to figure their month breakdown and cant even tell what month they are looking at for said breakdown.

 

Note, This report does have a 'Division' slicer on the right of the invoicing by month chart. We do not need this being incorporated into the title, however I mention in the event it needs to be factored into any formula design.

 

So theres two things here:

1) Layer 0 is fine, it may have a "generic" title

2) Layer 1 should be dynamic to show what data has been drilled into. 

 

I am open to the idea of suppressing default titles and using formulas, however would need some guidance here.

 

Thanks,

Andrew

1 ACCEPTED SOLUTION

Hi @andrmh,

 

The blog which BhaveshPatel provided is use card visual and slicer to achieve the dynamic title.

Hide the default title, and get the select value from slicer, then deal with these data and convert them to specify format.

 

For example.

 

Measure:

SelectValue =
var selected= ALLSELECTED(Cycle[DayOfWeek])
var leftPart=CONCATENATEX(TOPN(COUNTROWS(selected)-1,selected),[DayOfWeek]&",")//use topn and countrow to remove last record
var rightPart=EXCEPT(selected,TOPN(COUNTROWS(selected)-1,selected))//use except function to get the last record
var resultString= LEFT(leftPart,LEN(leftPart)-1)&" And "&rightPart//merge left part and right part
Return
"Dynamic title by "&if( COUNTROWS(selected)=1,ALLSELECTED(Cycle[DayOfWeek]),resultString)//check select records' count, if selected records' count greater or equal than 2, use merged result string

 

 

Create a card visual and drag this measure to it, then turn off the category label.

Capture.PNG

 

Create a slicer to choose the display value.

Capture2.PNG

 

Drag the card visual ot left top corner of the chart.

Capture3.PNG

 

Click on slicer to change the result string.

Capture4.PNG

 

Reference of Dax function:

DAX Function Reference

 

LEFT Function (DAX)

RIGHT Function (DAX)

LEN Function (DAX)

ALLSELECTED Function (DAX)

CONCATENATEX Function (DAX)

TOPN Function (DAX)

EXCEPT Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
BhaveshPatel
Community Champion
Community Champion

Hi @andrmh

 

Please visit this BLOG to get the exact solution. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Hi Bhavesh,

 

Thanks for the link.

 

Do drill downs behave as 'filters' this way?

 

I had stumbled by this blog before and had not managed to get it working as the example seems to be referencing slicer data.

Not being too familiar with DAX, if i make a simple formula:

 

selectedMonths = COUNTROWS(VALUES (Invoicing_ALL[Month] ))

 

At the upper level, I get a value of 5 (5 rows - July through November), when I drill down clicking on say July, that number is still 5.

 

There must be a way I am able to identify the context of what I have selected to drill into... Might even be missine something very simple.

 

Thanks.

Hi @andrmh,

 

The blog which BhaveshPatel provided is use card visual and slicer to achieve the dynamic title.

Hide the default title, and get the select value from slicer, then deal with these data and convert them to specify format.

 

For example.

 

Measure:

SelectValue =
var selected= ALLSELECTED(Cycle[DayOfWeek])
var leftPart=CONCATENATEX(TOPN(COUNTROWS(selected)-1,selected),[DayOfWeek]&",")//use topn and countrow to remove last record
var rightPart=EXCEPT(selected,TOPN(COUNTROWS(selected)-1,selected))//use except function to get the last record
var resultString= LEFT(leftPart,LEN(leftPart)-1)&" And "&rightPart//merge left part and right part
Return
"Dynamic title by "&if( COUNTROWS(selected)=1,ALLSELECTED(Cycle[DayOfWeek]),resultString)//check select records' count, if selected records' count greater or equal than 2, use merged result string

 

 

Create a card visual and drag this measure to it, then turn off the category label.

Capture.PNG

 

Create a slicer to choose the display value.

Capture2.PNG

 

Drag the card visual ot left top corner of the chart.

Capture3.PNG

 

Click on slicer to change the result string.

Capture4.PNG

 

Reference of Dax function:

DAX Function Reference

 

LEFT Function (DAX)

RIGHT Function (DAX)

LEN Function (DAX)

ALLSELECTED Function (DAX)

CONCATENATEX Function (DAX)

TOPN Function (DAX)

EXCEPT Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This is an excellent tutorial but I have an extended question!

 

I have the Card working to show the selected category when chosen without drill down turned on. However when drill down is ENABLED, and I click on a category it just keeps the default text.

 

Is there a way for this to carry over with the drill down turned on or is the slicer the only option? 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors