PowerBI Slicing the data on multiple columns values

[Khushi]

IN POwerbi we have data like 

MonThYear	ID	Suburb	Street	Area	Zone	City
Jun 2023	1	yes	no	no	no	no
Jun 2023	2	yes	yes	no	no	no
Jun 2023	3	no	yes	yes	yes	no
Jun 2023	4	no	no	no	no	no

I have a slicer  with values

Suburb

Street

Area

Zone

City

NOne

• IF Suburb is selected it should return row number 1
• IF Suburb and street both are  selected it should return row number 1 and row number 2 and row number 3
• IF area and street both are  selected it should return row number 2 and row number 3
• and IF NOne is selected it should return row number 4

Now i have to map this on StackBar chart with Month Year on X Axis and Count on Y axis

[Khushi · Accepted solution]

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)

[amitchandak]

@Khushi , Better to unpivot this data and then create a measure with values yes and slicer with attribute

countrows(filter(Table, Table[Value] = "Yes" ))

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

[Khushi]

Actually it is combination of two table

ID	Suburb	Street	Area	Zone	City
1	yes	no	no	no	no
2	yes	yes	no	no	no
3	no	yes	yes	yes	no
4	no	no	no	no	no

Id	IdName
1	A
2	B
3	C
4	D

and the data is coming from AAS and this table is used at many place so i cann't pivot.