cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dw700d
Helper V
Helper V

Need help a adjusting a measure to include blanks

Measure =

VAR _A =

    FILTER (

        SUMMARIZE (

            'Table',

            'Table'[LocCD],

            "Z",

                CALCULATE (

                    SUM ( 'Table'[Amt] ),

                    FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Trac] ) ) )

                )

        ),

        [Z] > 0

    )

RETURN

    SUMX ( _A, [S] )

 

 

This measure was created to identify any Loc CD with A first letter that begins with Z, where the Tract column = blank and the aggregate amount of all Loc CD entries combined is greater than 0. In the example below the measure  would Identify Loc CD Z5678 because the total amount is greater than 0,  it begins with Z and the tract column is blank. Currently the measure is showing Loc CD  items that have data in the Tract field. I would like to only view Loc CD items where tract column is blank. How can I adjust this measure to accomplish this?

 

Index    

LocCD             

Amount

        Tract              

1

Z0000

20

        ABC

2

Z1234

100

 

3

Z1234

-70

 

4

Z1234

-100

 

5

Z1234

20

 

6

Z5678

-50

 

7

Z5678

-10

 

8

Z5678

20

 

9

Z5678

80

 
1 ACCEPTED SOLUTION

Hi @dw700d ,

For a text data type, ISBLANK function always can't work as expected, you can modify it like this:

Measure = 
VAR _A =
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[LocCD],
            "Z",
                CALCULATE (
                    SUM ( 'Table'[Amt] ),
                    FILTER ( 'Table', 'Table'[Tract]=BLANK()) 
                )
        ),
        [Z] > 0
    )
RETURN
    SUMX ( _A, [Z] )

Get the result.

vkalyjmsft_0-1642757551833.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

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

7 REPLIES 7
parry2k
Super User
Super User

@dw700d remove NOT where it say NOT ISBLANK

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k  thanks for your input

when I remove "not" it gets rid of the filter that allows me to limit  Location Codes to those that begin with Z. in other words its shows me locations that begin with Z and other letters which doesnt work for me.

Hi @dw700d ,

For a text data type, ISBLANK function always can't work as expected, you can modify it like this:

Measure = 
VAR _A =
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[LocCD],
            "Z",
                CALCULATE (
                    SUM ( 'Table'[Amt] ),
                    FILTER ( 'Table', 'Table'[Tract]=BLANK()) 
                )
        ),
        [Z] > 0
    )
RETURN
    SUMX ( _A, [Z] )

Get the result.

vkalyjmsft_0-1642757551833.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

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

parry2k
Super User
Super User

@dw700d now makes sense, before it looks like the measure you showed is doing what you put in the description.

 

Measure =

VAR _A =

    FILTER (

        SUMMARIZE (

            'Table',

            'Table'[LocCD],

            "Z",

                CALCULATE (

                    SUM ( 'Table'[Amt] ),

                    NOT ( ISBLANK ( 'Table'[Trac] ) ),
                    LEFT ( 'Table'[LocCD], 1 ) = "Z"

                )

        ),

        [Z] > 0

    )

RETURN

    SUMX ( _A, [S] )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k  Something is a bit off, this measure is only giving me data where the "Tract" column contains information. I need the "Tract" column to be blank. For example, this measure returns Location Code Z0000 but that doesnt work for me because  that location code contains data in the tract column. The tract column needs to be blank

 

Index    

LocCD             

Amount

        Tract              

1

Z0000

20

        ABC

2

Z1234

100

 

3

Z1234

-70

 

4

Z1234

-100

 

5

Z1234

20

 

6

Z5678

-50

 

7

Z5678

-10

 

8

Z5678

20

 

9

Z5678

80

 
parry2k
Super User
Super User

@dw700d the measure you have shown and the description doesn't match:

 

- nowhere in the measure it is saying, sum where loccd starts with "Z"

- it is already summing up where track is not blank

 

Bit confused about your measure and the description you provided.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k  Thanks for the response. That measure was given to me in a different post in an attempt to address my request below. 

 

I would like a measure that identifies any Location Code with a first letter that begins with Z, where the Tract column = blank and the total value in the spend column is greater than 0. In the example below I have three"Location Codes" Z1234, Z5678 & Z0000 the measure would only return  an amount for "Location Code" Z5678  because the total value of all its transacations is greater than 0 (-50,-10,20,80). The amount would be 40.

 

The measure would not return an amount for Z1234 because the aggregate value of all Z1234 transactions is negative (100,-70,-100,20) -50

 

It would not return an amount for Location Code Z0000 because the Tract column is not blank

 

 

TransactionLocation Code   Spend         Tract              
1Z000020       ABC
2Z1234100 
3Z1234-70 
4Z1234-100 
5Z123420 
6Z5678-50 
7Z5678-10 
8Z567820 
9Z567880 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors