Not able to create a slicer

[abinashkhamari]

Hi Team,

I am facing an issue in creating a slicer. I have two tables. 

Site table

Country	SiteID	Site Name	Site Status
France	1	SN1	Open
France	2	SN2	Closed
France	3	SN3	Closed
France	4	SN4	Open
France	5	SN5	Open
France	6	SN6	Closed
France	7	SN7	Open
France	8	SN8	Open

Fact table and they are joined using siteID

Month	SiteID	KPI Name	KPI Value
Jan-21	1	kpi_cost	20
Jan-21	1	kpi_vol	99
Feb-21	1	kpi_cost	40
Feb-21	5	kpi_cost	43
Feb-21	5	kpi_vol	67

I have two calculated measures 

Volume = CALCULATE(SUM('Fact table'[KPI Value]),'Fact table'[KPI Name]="kpi_vol")

Cost = CALCULATE(SUM('Fact table'[KPI Value]),'Fact table'[KPI Name]="kpi_cost")

My requirement is to create a table as shown below but with two slicers

a) Site Status (which i am able to do) 

b) Volume slicer which will have two values 

    1. Volume Is Blank 

    2. Volume Is not Blank 

I am not able to create this slicer. Please help.

[sanalytics · Accepted solution]

@abinashkhamari 

Easiest way to achieve this by Buttons and selection instead of having slicer.

If you badly need a slicer then  can we have flexilibity to show 0 instead of Blank?

if yes, then below are steps

1) Crate New Volume Measure = 

IF( ISBLANK( [Volume] ),0,[Volume])

2) Create another measure which will show the Facttable SiteID

FactSiteID = 

IF(

MAX ( DimSite[SiteID] ) IN ALLSELECTED( FactTable[SiteID]),1,0)

3) Create a Disconnected table with Volume is 0 and Volume is not 0 and dragged it into a slicer

4) Create below set of measures

Volume is 0 =

VAR _Summarize =

SUMMARIZE( DimSite ,DimSite[Country],DimSite[SiteID],DimSite[Site Name],DimSite[Site Status],"Volume",[New Volume],"Filter",[FactSiteID] )

VAR _3 =

FILTER(

_Summarize,[Filter] = 0

)

VAR _result=

SUMX(

_3,[Volume] )

RETURN

_result

Volume is not 0 =

VAR _Summarize =

SUMMARIZE( DimSite ,DimSite[Country],DimSite[SiteID],DimSite[Site Name],DimSite[Site Status],"Volume",[New Volume],"Filter",[FactSiteID] )

VAR _3 =

FILTER(

_Summarize,[Filter] = 1

)

VAR _result =

SUMX(

_3,[Volume] )

RETURN

_result

5) Finally, create a consolidated measure

volume selection = 

IF(

SELECTEDVALUE('Volume Slicer'[Column1]) =1 ,

[Volume is 0],[Volume is not 0 ] )

Please have a look on below screenshot

Hope it will help you

Regards

sanalytics

If it is your solution then please like and accept it as solution