Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
PBIC,
Below link is to an .xlsx file with two tabs. Tab one is "raw data" and tab two is "expected results". I stopped manually entering my expected results in row 163 of column G.
https://1drv.ms/x/s!AiY07PwRMPgKije_YP9Z-DNyx_IB?e=8g5iEt
In raw data tab, column D named "County, ST" is sorted alphabetically. There are many cities within the same county name. For example, first county name is "Abbeville, SC"... and it contains 7 different cities.
In the same tab, the next column E, named "City, St.GmapsExt" contains both blanks and text of the city name.
I'm having trouble thinking of a DAX expression that says this:
If a group of cells that are the same in column D, is True, then look in the same row, but in column E. If ANY cell in column E contains text, within the group of cells that are the same in column D, then "YES", otherwise, "NO".
OR, the inverse (using Blank)
If a group of cells that are the same in column D, is True, then look in the same row, but in column E. If ALL cells in column E contains BLANK(), within the group of cells that are the same in column D, then "NO", otherwise, "YES".
Thank you in advance.
Ryan
Solved! Go to Solution.
Hi @tamerj1
I was able to take your start, using "blank" allvalues" and "except", do a little more digging, and I found a similar example which is a result I was looking for. Thanks for your help in starting out solution! I posted code below, and also updated *pbix file.
Column =
IF(
ISBLANK(
CALCULATE(
MAX('raw data'[City, ST.GmapsExt (Artikel)]),
( ALLEXCEPT('raw data','raw data'[County, ST (Auftgragsnummer)] ) ),
'raw data'[City, ST.GmapsExt (Artikel)] <> BLANK()
)
),
"NO COUNTY DATA",
"YES"
)
https://1drv.ms/u/s!AiY07PwRMPgKikE8uukhIDqxd9D0?e=bacTso
Thanks again!
-Ryan
oh... here was the other example I ended up finding that worked:
@ryan1919
Happy that you found a solution!
I was clearly working under a misunderstanding of your requirement. The reason is that I relied on my my interpretation of your original DAX code rather than carefully reading your description.
I thought the requirement says if at least one blank city in the county then "NO"... and that "YES" applies only if no blanks at all.
Anyway, this is how I would solve the problem.
Flag =
IF (
ISEMPTY (
FILTER (
CALCULATETABLE(
VALUES ( 'raw data'[City, ST.GmapsExt] ),
ALLEXCEPT ( 'raw data', 'raw data'[County, ST] )
),
'raw data'[City, ST.GmapsExt] <> BLANK ( )
)
),
"NO COUNTY DATA",
"YES"
)
Hi @tamerj1
I was able to take your start, using "blank" allvalues" and "except", do a little more digging, and I found a similar example which is a result I was looking for. Thanks for your help in starting out solution! I posted code below, and also updated *pbix file.
Column =
IF(
ISBLANK(
CALCULATE(
MAX('raw data'[City, ST.GmapsExt (Artikel)]),
( ALLEXCEPT('raw data','raw data'[County, ST (Auftgragsnummer)] ) ),
'raw data'[City, ST.GmapsExt (Artikel)] <> BLANK()
)
),
"NO COUNTY DATA",
"YES"
)
https://1drv.ms/u/s!AiY07PwRMPgKikE8uukhIDqxd9D0?e=bacTso
Thanks again!
-Ryan
Hi @ryan1919
please try
Flag =
IF (
BLANK ()
IN CALCULATETABLE (
VALUES ( 'Table'[Column E] ),
ALLEXCEPT ( 'Table', 'Table'[Column D] )
),
"No",
"Yes"
)
Hey @tamerj1
Thank you for the attempt.
I used the above code to enter into *.pbix file, however it did not work as anticipated.
I have shared a link to the *.pbix file below for reference.
https://1drv.ms/u/s!AiY07PwRMPgKikE8uukhIDqxd9D0?e=EFNrfs
I attempted to add a couple of columns to the side, to break down the code further, to see how the individual parts to the whole worked, but I could not see a clear item of issue.
-Ryan
Hey @ryan1919
If you sort by [County, ST]you will see that almost all counties have some blank cities. Unless I misunderstood the requirement, I can say it is working as intended.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
42 | |
37 | |
22 | |
22 | |
21 |