Forum Discussion
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 matrix visual only able to show "AMER" details for "From Region" while it could not show those details with "AMER" in "To Region" as shown table below:
My expected output should be:
The same issues go to Site & Subregion as well. How can I get my expected output as shown above?
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
4 Replies
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.
Hi Py0809 ,
I created a sample pbix file(see the attachment), please find the details in it.
Region Flag = VAR _fregion = SELECTEDVALUE ( 'Fact'[From Region] ) VAR _tregion = SELECTEDVALUE ( 'Fact'[To Region] ) VAR _regions = ALLSELECTED ( 'Dim_Region'[Region] ) RETURN IF ( _fregion IN _regions || _tregion IN _regions , 1, 0 )Best Regards
But when I try to filter Site = "WISCHE", the table does not apply the filter and still showing other site (COMKUN) as well. Is there any solution for this?
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
