Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

 


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. 

 

Thank you!

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

     

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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!