Filter Based on Measure to Exclude Rows

[newpbiuser01]

Hello,

I have a data table as follows and I am trying to use the Parameters functionality to dynamically switch between showing the Amount by Department and Location columns.  In addition to this, I need to filter out all rows where the Days Late is blank when the user wants to view the breakdown by Department. 

Data Table - Test:

DepartmentDays LateAmountLocation

Sales		10	US
Marketing	7 Days	20	CA
Sales	8 Days	10	AUS
Marketing		25	UK
R&D	10Days	565	US
R&D		897	CA
Sales		979	AUS

So, what I am trying to do is, use a Data Filtering measure to only show the rows where the Days Late columns <> null: 

Data Filtering - Blank Days Late =

    VAR selValTable1 = VALUES('Test')

    VAR selValTable2 = FILTER('Test', [Days Late] <> "")

    VAR DataBy = SELECTEDVALUE('Parameter'[Parameter Fields])

    RETURN

        SWITCH(TRUE(),

            DataBy = "'Test'[Department]", COUNTROWS(INTERSECT(selValTable1, selValTable2)),

            DataBy = "'Test'[Location]", COUNTROWS(selValTable1)

            )

I then use this measure as a filter in my visuals. This allows me to filter my data table to only include the three rows on a data table, , but when I use it on a barchart, instead of filtering out all the blank days late rows, I get the total sum (Even though I applied the fillter on the measure). Why is my measure not working on the barchart? Shouldn't it act like a filter like any other column based filters and only give me the sum of the amounts where the Days Late <> null? 

Thank you!

[v-yiruan-msft · Accepted solution]

Hi @newpbiuser01 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below, and put this measure onto the bar chart to replace the original value field.

Measure =
VAR DataBy =
    SELECTEDVALUE ( 'Parameter'[Parameter Fields] )
RETURN
    SWITCH (
        TRUE (),
        DataBy = "'Test'[Department]", CALCULATE ( SUM ( 'Test'[Amount] ), 'Test'[Days Late] <> BLANK () ),
        DataBy = "'Test'[Location]", SUM ( 'Test'[Amount] )
    )

Best Regards

[newpbiuser01]

Thank you @v-yiruan-msft! That worked like a charm.