DAX Power Pivot

[Simming2022]

Hi

I am using PowerPivot to build a pivot table.  I am wanting to have a slicer based off a function so I can better filter my pivot table.  

Example:  Is there a way to have a slicer that ties to the number in WKS_No_DATA?  WKS_No_Data is a summed column on the pivot table.   I would like to have a Slicer tied to any number that list in the WKS_No_Data so i can just filter on 1, 2, or 3)

I know I can use a simple filter at the top of the pivot but i would like a slicer so the pivot still shows grand totals at the bottom and does all the normal calculations.

	WKS_No_Data
	1
	2
	3
	1
	3

Thank you.

[tamerj1]

Hello again @Simming2022 
Please refer to attached sample file with the solution

You need to have a disconnected slicer table just containing the numbers from 1 to the max possible number as per your requirement. Then use the code indicated below for the measure. My example might be different than your actual case but the concept is the same. In this example I am counting the number of customers sliced by city name. The slicer filters the measure based on the selected number of of customers. If I select one it will show only the cities that have only one customer and so on.

=IF ( 
	HASONEVALUE ( 'Number of Customers'[Number of Customers] ),
	VAR CustomerNum = COUNTROWS ( Customers )
	VAR SelectNum = MAX ( 'Number of Customers'[Number of Customers] )
	RETURN
		IF (
			CustomerNum = SelectNum,
			CustomerNum
		)
)

[tamerj1]

Hi @Simming2022 

this can be done in power bi as it has a filter pane for each visual where you can place filter measures that filter the visual based on dax code. This functionality is not available in power pivot. However, I think this could be possible but I need to try it to confirm. I'll check later on today and get back to you.