Forum Discussion

quynhpham1955's avatar
quynhpham1955
Regular Visitor
2 years ago
Solved

Filter table based on Slicer

Hi,

 

I have a set of data from this link http://quynhpham.net/temp/sample.xlsx which includes of 6 worksheets.

"Actual" and "Budget" are from database and updated every month.

"Jul", "Aug", "Sep" and "Oct" are reports generated from Power BI based on Slicer selection.

 

I would like to use Power BI with visual Slicer and a visual table or matrix with requirement as below.

If select Jul from Slicer then the visual table will show data as worksheet "July", similarly if Aug selected then the visual table is auto reload with data as in worksheet "Aug".

 

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi quynhpham1955 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a month dimension table

    2. Append the table

    3. Create a measure as below to get the amount

    SAmount = 
VAR _selmonth =
    SELECTEDVALUE ( 'Months'[Month] )
RETURN
    CALCULATE (
        SUM ( 'Append1'[amount] ),
        FILTER (
            'Append1',
            IFERROR ( SEARCH ( _selmonth, 'Append1'[Month], 1, 0 ), 0 ) > 0
        )
    )

    4. Create a table visual

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi quynhpham1955 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a month dimension table

    2. Append the table

    3. Create a measure as below to get the amount

    SAmount = 
VAR _selmonth =
    SELECTEDVALUE ( 'Months'[Month] )
RETURN
    CALCULATE (
        SUM ( 'Append1'[amount] ),
        FILTER (
            'Append1',
            IFERROR ( SEARCH ( _selmonth, 'Append1'[Month], 1, 0 ), 0 ) > 0
        )
    )

    4. Create a table visual

    Best Regards