Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
@dw700d remove NOT where it say NOT ISBLANK
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.
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.
@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] )
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 |
@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
Transaction | Location Code | Spend | 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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |