Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

DAX Power Pivot

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.

 

Dax Power Pivot

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hello again Anonymous 
    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
		)
)