Forum Discussion
PowerBI Slicing the data on multiple columns values
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
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)
3 Replies
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
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.
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)
