Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
newpbiuser01
Helper V
Helper V

Filter Based on Measure to Exclude Rows

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 10US
Marketing7 Days20CA
Sales8 Days10AUS
Marketing 25UK
R&D10Days565US
R&D 897CA
Sales 979AUS

 

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? 

newpbiuser01_1-1682353637114.png

Thank you!

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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] )
    )

 

vyiruanmsft_0-1682492117436.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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] )
    )

 

vyiruanmsft_0-1682492117436.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.