Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
ryan1919
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION
ryan1919
Frequent Visitor

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 

 

ryan1919_0-1686841251261.png

Thanks again!

-Ryan

View solution in original post

6 REPLIES 6
ryan1919
Frequent Visitor

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

1.png

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
Frequent Visitor

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 

 

ryan1919_0-1686841251261.png

Thanks again!

-Ryan

tamerj1
Super User
Super User

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.

1.png

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.