topic trouble "thinking in DAX" WRT new calculated column based on two other columns in DAX Commands and Tips

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

ryan1919 — Mon, 12 Jun 2023 16:02:22 GMT

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

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

tamerj1 — Mon, 12 Jun 2023 18:00:00 GMT

Hi ryan1919

please try

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

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

ryan1919 — Thu, 15 Jun 2023 10:02:53 GMT

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

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

tamerj1 — Thu, 15 Jun 2023 10:47:32 GMT

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.

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

ryan1919 — Thu, 15 Jun 2023 15:01:36 GMT

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

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

ryan1919 — Thu, 15 Jun 2023 15:02:35 GMT

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-in-column-1-when-a-value-in/m-p/2710414#M82300

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

tamerj1 — Thu, 15 Jun 2023 15:47:15 GMT

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