Multiple Sections or Select All in Filter Slicer Not Displaying Column Values

[Palan22]

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 

Target Goal Per Month = CALCULATE(MAX('Goals'[DOS]),FILTER(ALL('Goals'),'Goals'[Goal Date].[Month]= SELECTEDVALUE(Service[Service Date].[Month]) && 'Goals'[Status] = "Completed"))

[HarishKM]

@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 .

[v-yangliu-msft · Accepted solution]

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.