![fireworks-574739_960_720.jpg](https://quantinsightsnetwork.com/wp-content/uploads/2023/10/fireworks-574739_960_720-678x381.jpg)
How to display the Count of Items in the Slicer List in Power BI
Scenario :
Suppose we have a dataset that shows the Sales information by Product as shown below :
Now our requirement is to show the Count of each Product in the Slicer as shown below.
We can achieve this by using the Calculated Table created from below DAX Expressions :
Summarize Method :
_ProdSummary = SUMMARIZE(SalesByCustomer, SalesByCustomer[ProdName],
“_ProdCount”,COUNT(SalesByCustomer[ProdName])+ COUNTBLANK(SalesByCustomer[ProdName]),
“_ProdNameCount”,CALCULATE(IF(ISBLANK(VALUES(SalesByCustomer[ProdName])),
“Blank()”,VALUES(SalesByCustomer[ProdName])) & ” (” & COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName]))&“)”)
AddColumns Method :
_ProdSummary = ADDCOLUMNS(SUMMARIZECOLUMNS(SalesByCustomer[ProdName],
“_ProdCount”,COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName])),
“_ProdNameCount”,CALCULATE(IF(ISBLANK(VALUES(SalesByCustomer[ProdName])),
“Blank()”,VALUES(SalesByCustomer[ProdName])) & (“& COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName])&”)”))
here, _ProdSummary is the Calculated Table ;
SalesByCustomer is the source Table from which we are Summarizing the data by the Column [ProdName] and finding the Count(including Blanks).
Result :
Notes :
——————————————-
To effectively use this Slicer, we have to Map this Calculated Table with the Source Table as shown in below Model :
Since there were Blank() values in the Source table “SalesByCustomer”, we are not able to establish the Many-One relation ship with Summary table. So that we chosen Many-Many relationship in this example.
Now we can validate the Data slicing , which will work as expected, shown below :
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment