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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.