Forum Discussion
PowerBI Slicing the data on multiple columns values
- 2 years ago
Created below measure and it is working
VAR SelectedOptions =
CONCATENATEX(
ALLSELECTED('SlicerRequest-Selection'),
'SlicerRequest-Selection'[MeasureName],
","
)VAR SuburbRecords =
CALCULATETABLE (
'Table_Fact',CONTAINSSTRING(SelectedOptions, "Suburb") &&
'Table_Fact'[Suburb]="Yes"
)VAR streetRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "street") &&
'Table_Fact'[street] ="Yes"
)
VAR AreaRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "Area") &&
'Table_Fact'[Area] ="Yes"
)VAR ZoneRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "Zone") &&
'Table_Fact'[Zone] ="Yes"
)RETURN
SUMX(DISTINCT( UNION (
SELECTCOLUMNS(SuburbRecords, "Suburb",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(streetRecords, "Street",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(AreaRecords, "Area",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(ZoneRecords, "Zone",1,"ID",'Table_Fact'[ID])
)),1)
Created below measure and it is working
VAR SelectedOptions =
CONCATENATEX(
ALLSELECTED('SlicerRequest-Selection'),
'SlicerRequest-Selection'[MeasureName],
","
)
VAR SuburbRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "Suburb") &&
'Table_Fact'[Suburb]="Yes"
)
VAR streetRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "street") &&
'Table_Fact'[street] ="Yes"
)
VAR AreaRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "Area") &&
'Table_Fact'[Area] ="Yes"
)
VAR ZoneRecords =
CALCULATETABLE (
'Table_Fact',
CONTAINSSTRING(SelectedOptions, "Zone") &&
'Table_Fact'[Zone] ="Yes"
)
RETURN
SUMX(DISTINCT( UNION (
SELECTCOLUMNS(SuburbRecords, "Suburb",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(streetRecords, "Street",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(AreaRecords, "Area",1,"ID",'Table_Fact'[ID]),
SELECTCOLUMNS(ZoneRecords, "Zone",1,"ID",'Table_Fact'[ID])
)),1)