Need Help with Button Text Change Upon Date Filter in Power BI

[HamidBee]

Hi All, 

I am working on a Power BI report and facing an issue with dynamic button text that is dependent on the filter state of a date field.

Here are the details:

1. I have a table named 'Jobs' with columns 'Entry Date' and 'Total'.
2. I have created a measure to sum the 'Total' column.
3. I have a line chart with 'Entry Date' (formatted as mm/yyyy) on the x-axis and the 'Total' measure on the y-axis.
4. I have two buttons with DAX formulas to change their text based on the filter status of 'Entry Date':

Drill Through Button Text =
VAR IsEntryDateFiltered = HASONEFILTER('Jobs'[Entry Date])

RETURN
IF(
IsEntryDateFiltered,
"Click Here",
"Select Filter"
)

UserDrill Through Button Text Navigation =
VAR IsEntryDateFiltered = HASONEFILTER('Jobs'[Entry Date])

RETURN
IF(
IsEntryDateFiltered,
"Page 2",
""
)

The issue is that when a user clicks a marker on the line chart, which applies a filter on the 'Entry Date', the button text is not updating to "Click Here" as intended.

Please find a link to the file below:

https://www.mediafire.com/file/lngumfrit7puqy2/Navigation.pbix/file

I suspect the problem might be related to how the 'Entry Date' field is being filtered when interacting with the line chart. It seems like the HASONEFILTER function is not recognizing the filter context applied from the chart interaction.

Could anyone suggest what I might be doing wrong or how I can troubleshoot this issue further?

Any insights or assistance would be greatly appreciated!

Thank you in advance.

@Ritaf1983 , @lbendlin , @Ashish_Mathur , @ryan_mayu , @parry2k , @amitchandak @sevenhills 

[sevenhills · Accepted solution]

For the first part i.e., updating the button text, you can do this.

a) change the measure 

Drill Through Button Text = 
 var _c1 = COUNTROWS( ALLSELECTED(Jobs[Entry Date]))
 var _c2 = COUNTROWS( VALUES ( Jobs[Entry Date] ))
 var _t = IF (_c1 <> _c2 && _c2 >= 1, CONCATENATEX ( VALUES ( Jobs[Entry Date] ) , [Entry Date] , ",") )
 RETURN -- IF ( ISBLANK(_t), "Click Here", _t)
 IF ( ISBLANK(_t), "Select Filter", "Click Here")

b) Select the button and go to the style button, change these settings

Test it, you will see the button changing !

For Part 2 i.e., second measure you can do similar changes and modify the destination setting. or upload the right file, where it is going wrong.

Hope this helps!

[littlemojopuppy]

Hi @HamidBee 

I think I found the problem...looked at the chart and what's on the X axis is year and month for Entry Date, not Entry Date itself.

When I changed the axis to use the actual date. what you had worked.

The two cards were me testing if the logical condition was met in your original and something I had rewritten.  Just change the axis to use the actual date and you should be golden.

Hope this helps and be good!  😊

[HamidBee]

Hi @littlemojopuppy 

Thanks for the reply. Sorry, I should I have mentioned that this was done intentionally. I want the chart to show only Month and Date on the x axis. The idea is that this chart would show the data on a summarized level. The user then filters the data by pressing a marker and then clicks the button to navigate to a more detailed page. 

I greatly appreciate your reply though.

[sevenhills · Accepted solution]

For the first part i.e., updating the button text, you can do this.

a) change the measure 

Drill Through Button Text = 
 var _c1 = COUNTROWS( ALLSELECTED(Jobs[Entry Date]))
 var _c2 = COUNTROWS( VALUES ( Jobs[Entry Date] ))
 var _t = IF (_c1 <> _c2 && _c2 >= 1, CONCATENATEX ( VALUES ( Jobs[Entry Date] ) , [Entry Date] , ",") )
 RETURN -- IF ( ISBLANK(_t), "Click Here", _t)
 IF ( ISBLANK(_t), "Select Filter", "Click Here")

b) Select the button and go to the style button, change these settings

Test it, you will see the button changing !

For Part 2 i.e., second measure you can do similar changes and modify the destination setting. or upload the right file, where it is going wrong.

Hope this helps!

[HamidBee · Accepted solution]

Thanks for the reply. The measure you made for 'Drill Through Button Text' does work however I was having an issue now with the navigation measure. It just wouldn't navigate to the desired page. I imagie it was due to the measure. So I changed it to this:

Drill Through Button Text = 
VAR IsEntryDateFiltered = ISFILTERED(Jobs[Entry Date]) || NOT(ISBLANK(SELECTEDVALUE(Jobs[Entry Date])))
RETURN IF(IsEntryDateFiltered, "Click Here", "Select Filter")

So just to recall, the page navigation measure is:

User Drill Through Button Text Navigation = 
VAR IsEntryDateFiltered = ISFILTERED(Jobs[Entry Date]) || HASONEVALUE(Jobs[Entry Date])
RETURN IF(IsEntryDateFiltered, "Page 2", BLANK())

 Thank you very much for your help though. It's greatly appreciated. 

[sevenhills]

Glad to hear it helped. Regards

[v-cgao-msft]

Hi @HamidBee ,

Please try this:

VAR __slicer_count = COUNTROWS(ALLSELECTED('Jobs'[Entry Date]))
VAR __all_count = COUNTROWS(ALL('Jobs'[Entry Date]))
VAR IsEntryDateFiltered = __all_count = __slicer_count

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[HamidBee]

Can you please explain what I'm supposed to do with this?. You have created variables but have not called them. This isn't helpful at all.

[v-cgao-msft]

Hi @HamidBee ,

My presentation is a bit vague. Here is the code:

Drill Through Button Text =
VAR __slicer_count = COUNTROWS(ALLSELECTED('Jobs'[Entry Date]))
VAR __all_count = COUNTROWS(ALL('Jobs'[Entry Date]))
VAR IsEntryDateFiltered = __all_count = __slicer_count

RETURN
IF(
IsEntryDateFiltered,
"Click Here",
"Select Filter"
)

UserDrill Through Button Text Navigation =
VAR __slicer_count = COUNTROWS(ALLSELECTED('Jobs'[Entry Date]))
VAR __all_count = COUNTROWS(ALL('Jobs'[Entry Date]))
VAR IsEntryDateFiltered = __all_count = __slicer_count

RETURN
IF(
IsEntryDateFiltered,
"Page 2",
""
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[HamidBee]

This does not work. I have provided a link to the direct pbix file in the question I posted. Please try using it.