Forum Discussion
Py0809
2 years agoFrequent Visitor
Filter two columns together
I have a fact table in PBI as below: I have the dimension tables linked to the fact table for all the region, subregion and site as well: If I choose the slicer "Region" = "AMER", The ...
- Anonymous2 years ago
Hi Py0809 ,
I updated my sample pbix file(see the attachment), please find the details in it.
1. Create the measures as below
Region Flag = VAR _fregion = SELECTEDVALUE ( 'Fact'[From Region] ) VAR _tregion = SELECTEDVALUE ( 'Fact'[To Region] ) VAR _regions = ALLSELECTED ( 'Dim_Region'[Region] ) RETURN IF ( HASONEFILTER ( Dim_Region[Region] ), IF ( _fregion IN _regions || _tregion IN _regions, 1, 0 ), 1 )Subregion Flag = VAR _fsregion = SELECTEDVALUE ( 'Fact'[From Subregion] ) VAR _tsregion = SELECTEDVALUE ( 'Fact'[To Subregion] ) VAR _subregions = ALLSELECTED ( 'Dim_Subregion'[Subregion] ) RETURN IF ( HASONEFILTER ( Dim_Subregion[Subregion] ), IF ( _fsregion IN _subregions || _tsregion IN _subregions, 1, 0 ), 1 )Site Flag = VAR _fsite = SELECTEDVALUE ( 'Fact'[From Site] ) VAR _tsite = SELECTEDVALUE ( 'Fact'[To Site] ) VAR _sites = ALLSELECTED ( 'Dim_Site'[Site] ) RETURN IF ( HASONEFILTER ( 'Dim_Site'[Site] ), IF ( _fsite IN _sites || _tsite IN _sites, 1, 0 ), 1 )2. Create the visual and apply the visual-level filter with the condition (Region Flag is 1, Subregion Flag is 1 and Site is 1)
Best Regards
scottostanek
2 years agoFrequent Visitor
Can you make a new column to combine From Region and To Region and filter on contains "AMER" ? This adds in complexity. You either have to hard code the options or set up a formula to return "AMER" if from or two contain it, which leaves out the idea you may also want to do the same for "EMEAF" in the same filter. Unless each item in the "filter" box were 5 separate radio buttons for the regions, with each testing for contained in the combined field.