Forum Discussion
Not able to create a slicer
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
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 SiteIDFactSiteID =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 slicer4) Create below set of measuresVolume 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_resultVolume 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_result5) Finally, create a consolidated measurevolume selection =IF(SELECTEDVALUE('Volume Slicer'[Column1]) =1 ,[Volume is 0],[Volume is not 0 ] )Please have a look on below screenshotHope it will help you
Regards
sanalytics
If it is your solution then please like and accept it as solution
1 Reply
sanalyticsSuper User
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 SiteIDFactSiteID =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 slicer4) Create below set of measuresVolume 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_resultVolume 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_result5) Finally, create a consolidated measurevolume selection =IF(SELECTEDVALUE('Volume Slicer'[Column1]) =1 ,[Volume is 0],[Volume is not 0 ] )Please have a look on below screenshotHope it will help you
Regards
sanalytics
If it is your solution then please like and accept it as solution