trouble "thinking in DAX" WRT new calculated column based on two other columns

[ryan1919]

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

[ryan1919]

oh... here was the other example I ended up finding that worked:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Filter-multiple-Rows-with-same-value... 

[tamerj1]

@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"
)

[ryan1919 · Accepted 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

[tamerj1]

Hi @ryan1919 

please try

Flag =
IF (
BLANK ()
IN CALCULATETABLE (
VALUES ( 'Table'[Column E] ),
ALLEXCEPT ( 'Table', 'Table'[Column D] )
),
"No",
"Yes"
)

[ryan1919]

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

[tamerj1]

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.