SUMX dynamically filtered by slicer

[Victor_Z]

Hello everyone

I have a table of incidents with multiple columns about  the incident (ticket number, date, time, countries affected, impact time, type of impact, root cause, etc...)

On the other hand I have the following measure for different purposes and calculations:

Impact_sg = SUMX(GROUPBY('Table_of_Incidents','Table_of_Incidents'[Impacted Service],'Table_of_Incidents'[Bussiness Process],'Table_of_Incidents'[SLA Impact sg],'Table_of_Incidents'[Impact Type],'Table_of_Incidents'[Affected Panels],'Table_of_Incidents'[Affected Countries]),'Table_of_Incidents'[SLA Impact sg])

Now we want to dinamically calculate Impact_sg measure based upon slicer selection from the enduser. 
The slicer selection consists of giving the end user the option of Add or Remove those incidents of Table_of_Incidents that match with a specific condition "Root Cause = External Conditions".

I am trying the following DAX formula but it doesn´t work.

New Impact _sg Measure =

VAR ExcludeExternal = SELECTEDVALUE('Incidents Analysis'[Root Cause])

RETURN

SUMX(

        GROUPBY(

         FILTER('Table_of_Incidents', NOT(ExcludeExternal) || 'Table_of_Incidents'[Root Cause] <> "External conditions"),

         'Table_of_Incidents'[Impacted Service],

        'Table_of_Incidents'[Bussiness Process],

        'Table_of_Incidents'[SLA Impact sg],

        'Table_of_Incidents'[Impact Type],

        'Table_of_Incidents'[Affected Panels],

        'Table_of_Incidents'[Affected Countries]),

    'Table_of_Incidents'[SLA Impact sg])

How can I manage to dinamically add/remove those rows of the table that match the criteria?

Any clue?  

Thanks in advance for your help. 

[HarishKM]

@Victor_Z Hey,
I will use below measure .

New Impact_sg Measure =
VAR ExcludeExternal =
SELECTEDVALUE ( 'Incidents Analysis'[Root Cause], "Include All" ) // Default value if slicer not selected
RETURN
SUMX (
GROUPBY (
FILTER (
'Table_of_Incidents',
IF (
ExcludeExternal = "External conditions",
'Table_of_Incidents'[Root Cause] <> "External conditions",
// Exclude External Conditions
TRUE () // Include all others
)
),
'Table_of_Incidents'[Impacted Service],
'Table_of_Incidents'[Bussiness Process],
'Table_of_Incidents'[SLA Impact sg],
'Table_of_Incidents'[Impact Type],
'Table_of_Incidents'[Affected Panels],
'Table_of_Incidents'[Affected Countries]
),
'Table_of_Incidents'[SLA Impact sg]
)

Thanks

Harish KM

If these steps help resolve your issue, your acknowledgment would be greatly appreciated.

[v-sshirivolu · Accepted solution]

Hi @Victor_Z ,
Thank you for reaching out to Microsoft Fabric community forum.

Create the disconnected slicer table
RootCauseFilter =
DATATABLE(
"FilterOption", STRING,
{
{"Include External Conditions"},
{"Exclude External Conditions"}
}
)

Add the slicer visual
Use - RootCauseFilter[FilterOption] as the field.
User can now choose to Include or Exclude External Conditions

Create the dynamic measure

Dynamic Impact_sg =
VAR SelectedOption = SELECTEDVALUE('RootCauseFilter'[FilterOption], "Include External Conditions")
VAR IsExclude = SelectedOption = "Exclude External Conditions"

RETURN
SUMX(
GROUPBY(
FILTER(
'Table_of_Incidents',
NOT(IsExclude) || 'Table_of_Incidents'[Root Cause] <> "External Conditions"
),
'Table_of_Incidents'[Impacted Service],
'Table_of_Incidents'[Bussiness Process],
'Table_of_Incidents'[SLA Impact sg],
'Table_of_Incidents'[Impact Type],
'Table_of_Incidents'[Affected Panels],
'Table_of_Incidents'[Affected Countries]
),
'Table_of_Incidents'[SLA Impact sg]
)

Add a card or table to show output

Drag in the Dynamic Impact measure.

Select slicer option and watch values update:

Include - Includes all rows.

Exclude - Excludes those with Root Cause = External Conditions.

Please find the attached .pbix file for your reference.

Regards,
Sreeteja.

[Victor_Z]

Thanks a lot. I will try and i´ll back to you with the results. 

[v-sshirivolu]

Hi @Victor_Z ,
Thank you for the update. If you encounter any issues or need additional assistance, please don't hesitate to reach out. 

[Victor_Z]

I worked thanks a lot. 

[rajendraongole1]

Hi @Victor_Z  - you can create one disconnected table with include and exclude condition as below

use calculated table:

ExternalFilterOption = DATATABLE(
"FilterChoice", STRING,
{
{"Include External Conditions"},
{"Exclude External Conditions"}
}
)

we can add this as slicer to report. ExternalFilterOption[FilterChoice] . 

Now create main measure:

Dynamic Impact_sg =
VAR FilterChoice = SELECTEDVALUE('ExternalFilterOption'[FilterChoice], "Include External Conditions")
VAR ApplyFilter = FilterChoice = "Exclude External Conditions"

VAR FilteredTable =
FILTER(
'Table_of_Incidents',
NOT ApplyFilter || 'Table_of_Incidents'[Root Cause] <> "External Conditions"
)

RETURN
SUMX(
GROUPBY(
FilteredTable,
'Table_of_Incidents'[Impacted Service],
'Table_of_Incidents'[Bussiness Process],
'Table_of_Incidents'[SLA Impact sg],
'Table_of_Incidents'[Impact Type],
'Table_of_Incidents'[Affected Panels],
'Table_of_Incidents'[Affected Countries]
),
'Table_of_Incidents'[SLA Impact sg]
)

This works, please check .