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
dw700d
Post Patron
Post Patron

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
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.