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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summarize Dax (without summarizing text strings that are blank)

Hello I have the following Dax which aims at Summarizing all aircraft who that have a value of $100,000 or more per Class which also have a Correct Company Name, but my output is also including $110,000 which is being calculated (Bellanca $20,000 plus Lancair $90,000). Although it is true that they are part of the Single Piston Class and are over $100,000, they are not in my Correct Company Name list and therfore I do not want them to be calculated. What can I add to this Dax so it avoids calculating a blank value in Correct Company Name? 

 

100k+ (SUMMARIZE) = 
SUMX(
    FILTER(
        SUMMARIZE(
        Database,
        'Class Lookup'[Class],
        CompanyNameLookup[Correct Company Name],
         "_1",
         SUM(Database[Amount])
         ),
         [_1]>=100000
         )
         ,[_1]
         )

 

 

Capture.JPG
THe correct answer I am looking for should not include the calculation of the SIngle Piston Column which has a blank value under Correct Company name column. 

 

My database and Lookup tables look like this. 

Database.JPG

 

Thank you!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please try this expression instead.  You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        FILTER (
            SUMMARIZE (
                Database,
                'Class Lookup'[Class],
                CompanyNameLookup[Correct Company Name],
                "_1"SUM ( Database[Amount] )
            ),
            NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
        ),
        [_1] >= 100000
    ),
    [_1]
)

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

Please try this expression instead.  You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        FILTER (
            SUMMARIZE (
                Database,
                'Class Lookup'[Class],
                CompanyNameLookup[Correct Company Name],
                "_1"SUM ( Database[Amount] )
            ),
            NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
        ),
        [_1] >= 100000
    ),
    [_1]
)

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


dedelman_clng
Community Champion
Community Champion

Hi @Anonymous -

 

Please try this

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        SUMMARIZE (
            Database,
            'Class Lookup'[Class],
            CompanyNameLookup[Correct Company Name],
            "_1", SUM ( Database[Amount] )
        ),
        [_1] >= 100000 && NOT( ISBLANK( [Correct Company Name] ) )
    ),
    [_1]
)

If that doesn't work, please provide you data in a format that can be copy/pasted into Excel or Power BI, rather than a screen shot.

 

Hope this helps

David

Anonymous
Not applicable

Thanks to both of you. I have taken an advanced Dax Course, but the difficulty for me is how to combine multiple Dax Expressions into the correct order to make them work for me. For instance I know I can get my answer by deselecting blank on my filter so I was trying to incorporate it in the Dax, but not sure where it needed to go in my formula. Thanks again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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