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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mgils24
Regular Visitor

Removing blanks from a defined variable in DAX/Power Query

Hi all,

 

This is driving me crazy. In my calculated table I want to remove the rows which contain a blank. A very simple task but I can't get it working. Any help is appreciated! 

I combine to columns and filter out the duplicates. This works fine but the column contains still 1 blank which I want to get rid off. The code looks very simple. As example I tried FILTER which doesn't work.

mgils24_0-1732878345126.png

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can use

Bridge SO =
VAR MNSO =
    FILTER (
        DISTINCT ( mn_rcpl[ServiceOrderNumber] ),
        NOT ISBLANK ( mn_rcpl[ServiceOrderNumber] )
    )
VAR PUSO =
    FILTER (
        DISTINCT ( 'Parts Usage Frequency Report - Raw Data'[Service Order] ),
        NOT ISBLANK ( 'Parts Usage Frequency Report - Raw Data'[Service Order] )
    )
VAR CombinedSO =
    DISTINCT ( UNION ( MNSO, PUSO ) )
RETURN
    CombinedSO

View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

hi @mgils24 ,

 

Could you try to replace both VALUES with DISTINCT? Will it also get what you expect?

mgils24
Regular Visitor

Thanks @johnt75 ! This works.

dharmendars007
Super User
Super User

Hello @mgils24 , 


The issue likely arises because FILTER expects a table and you're passing a column expression. By wrapping the column in FILTER directly within the RETURN statement, this will resolve the problem

BridgeSO =
VAR MWSO = VALUES(mn_rcp[ServiceOrderNumber])
VAR PUSO = VALUES('Parts Usage frequency report + Raw data'[Service Order])
VAR CombinedSO = UNION(MWSO, PUSO)
VAR CombinedSO2 = DISTINCT(CombinedSO)
RETURN
FILTER(CombinedSO2, NOT(ISBLANK(CombinedSO2)))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

 

johnt75
Super User
Super User

You can use

Bridge SO =
VAR MNSO =
    FILTER (
        DISTINCT ( mn_rcpl[ServiceOrderNumber] ),
        NOT ISBLANK ( mn_rcpl[ServiceOrderNumber] )
    )
VAR PUSO =
    FILTER (
        DISTINCT ( 'Parts Usage Frequency Report - Raw Data'[Service Order] ),
        NOT ISBLANK ( 'Parts Usage Frequency Report - Raw Data'[Service Order] )
    )
VAR CombinedSO =
    DISTINCT ( UNION ( MNSO, PUSO ) )
RETURN
    CombinedSO

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.