Forum Discussion
Multiple Sections or Select All in Filter Slicer Not Displaying Column Values
Hi,
I have a column that should display the posted service date's monthly target goal based on the month of the service date listed for completed services, but when I attempt to filter the report to display all of the past months targe goals the column becomes blank. In other words, the column will only display the target goal if the filter applied has one month selected at a time. Any way I can update my DAX to allow for more than one month selection and still display the target goal values for each month? I tried searching through past forums but was unsuccessful in finding a solution.
Here is an example of the filter slicer I am using and what is currently displayed when 'All' is selected vs. when one month is selected at a time:
The DAX used for the target goal column is
Hi Palan22 ,
According to your description, I create some data:
Here are the steps you can follow：
1. Create calculated table.
Service = CALENDARAUTO()2. Create calculated column.
Month = FORMAT('Service'[Service Date],"mmm")Month = FORMAT('Goals'[Goal Date],"mmm")3. Create measure.
Target Goal Per Month = var _select=SELECTEDVALUE('Service'[Month]) return CALCULATE(MAX('Goals'[DOS]), FILTER(ALL('Goals'),'Goals'[Month]= _select && 'Goals'[Status] = "Completed"))4. Put [Status] in Filter and select Completed
5. Result:
When choosing ALL,
When choosing May,
If this result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Palan22 ,
According to your description, I create some data:
Here are the steps you can follow：
1. Create calculated table.
Service = CALENDARAUTO()2. Create calculated column.
Month = FORMAT('Service'[Service Date],"mmm")Month = FORMAT('Goals'[Goal Date],"mmm")3. Create measure.
Target Goal Per Month = var _select=SELECTEDVALUE('Service'[Month]) return CALCULATE(MAX('Goals'[DOS]), FILTER(ALL('Goals'),'Goals'[Month]= _select && 'Goals'[Status] = "Completed"))4. Put [Status] in Filter and select Completed
5. Result:
When choosing ALL,
When choosing May,
If this result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- HarishKM
Super User
Palan22 Hey ,
You can create a new month name coloum from service date .
use below formula to create a col.
month year name = format(tablename[servicedate],"Mmm-YY" )
drag the coloum as slicer then try to slice and dice report .
if you are using date table create a relationship in between both table . ( if you have correct relationship active)
if not create a key for defining relationship.
key = DATEVALUE('data table'[Date]) do it for both table and then define relationship of both table by that.if you find it useful then Kudo will aprriciated and accept the solution .
