Forum Discussion
SUMX dynamically filtered by slicer
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.
Hi Anonymous ,
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 measureDynamic 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 outputDrag 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.
6 Replies
- rajendraongole1
Super User
Hi Anonymous - 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 .
Hi Anonymous ,
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 measureDynamic 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 outputDrag 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.- HarishKM
Anonymous 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.
