Forum Discussion

Py0809's avatar
Py0809
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • Py0809's avatar
    Py0809
    Frequent Visitor
    2 years ago

    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?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 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