Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI wizards and witches,
I have an issue I don't know how to handle, so please help.
I have two tables: Agreement and Opp. They are linked by a one-to-many relationship (Agreement: one; Opp: many) via the field 'Agreement Number'.
The Opp contains two columns: Agreement Number and Opp Number.
Not all Agreements have Opp Numbers.
I'd like to have a count of how many Agreement Numbers don't have any Opp Numbers.
So, in the Agreement table, I've added the following measure:
Agreements w/o Opps =
CALCULATE(
DISTINCTCOUNT('Agreement'[Agreement Number'),
ISBLANK(Opp[Opp Number])=TRUE()
)
I get a blank 😞
I swear there are some, as I listed them in a table (Agreement[Agreement Number] and Opp[Opp Number], and clicked on 'Show items with no data'.
Thank you so much,
Alice
Solved! Go to Solution.
@AliceW try this measure
Measure =
CALCULATE (
COUNTROWS ( Agreement ),
FILTER (
SUMMARIZE ( Agreement , Agreement[Agreement], "_c", COUNTROWS ( Opp ) ),
[_c] = BLANK()
)
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.
# Agg wo Opp :=
CALCULATE (
DISTINCTCOUNT ( 'Agreement'[Agreement Number] ),
Opp[Opp Number] = BLANK ()
)
Hi Antriksh,
I still get a blank. I have no idea why.
Update: I changed the cross filter direction to 'one way' and it works. But why does it work?
Shouldn't it work with both options?
Thank you!
Alice
I know, it's weird, right? I used this kind of measure before and it worked!
Thank you so much for taking the time to answer - and create a dummy model!!
@AliceW try this measure
Measure =
CALCULATE (
COUNTROWS ( Agreement ),
FILTER (
SUMMARIZE ( Agreement , Agreement[Agreement], "_c", COUNTROWS ( Opp ) ),
[_c] = BLANK()
)
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.
Thank you so much, Parry! Problem solved!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |