One-to-many relationship and calculated columns

[giogiogio]

Hi everyone,

I have two tables with relationship "one-to-many" linked by column "ID" where:
- Table1 has unique ID values
- Table2 has a subset of those ID, but potentially repeated multiple times (up to 3). Table2 also have a column "Cat" that if filtered by Cat = "A" makes the list of ID in Table2 unique again (teoretically the relationship comes back to a "one-to-one" with Table1).

Question is: how can I set up a DAX code for a calculated column in Table1 that returns "YES" if Table2 has an entry for that ID and has Cat="A" and "NO" if there is no ID in Table2 or if there is the ID but with Cat<>"A"?

As of now I have a Table3 that has unique IDs and multiple columns for the different "Cat" to have the one-to-one relationship and the RELATED function working, but I'd like to get rid of this duplication.

Thanks!

[amitchandak · Accepted solution]

@giogiogio ,

A new column in Table 1

if(isblank(countx(filter(table2,table1[ID] = table2[ID] and table2[Cat] ="A"),table2[Cat])),"No","Yes")

[giogiogio]

Thank you both gents, both solutions are working fine!
@amitchandak  @az38 

[amitchandak · Accepted solution]

@giogiogio ,

A new column in Table 1

if(isblank(countx(filter(table2,table1[ID] = table2[ID] and table2[Cat] ="A"),table2[Cat])),"No","Yes")

[az38]

Hi @giogiogio 

you can create a measure

Measure = 
var _count = CALCULATE(COUNTROWS(Table2), FILTER(ALL(Table2), Table2[ID] = SELECTEDVALUE(Table1[ID] && Table2[Cat] = "A") )
RETURN
IF(_count > 0, "YES", "NO")

and I don't understand why do you need third table. I'm sure there could be more appropriate solution

[giogiogio]

@az38 thanks for the hint.
That is for a measure, but I am trying to find a way to create a calculated column in Table1 as I would like to use this also as a slicer/category in charts and not only as a measure

[az38]

@giogiogio

the same but with EARLIER()

Column= 
var _count = CALCULATE(COUNTROWS(Table2), FILTER(ALL(Table2), Table2[ID] = EARLIER(Table1[ID]) && Table2[Cat] = "A") )
RETURN
IF(_count > 0, "YES", "NO")

[az38 · Accepted solution]

@giogiogio 

the same but with EARLIER()

Column= 
var _count = CALCULATE(COUNTROWS(Table2), FILTER(ALL(Table2), Table2[ID] = EARLIER(Table1[ID]) && Table2[Cat] = "A") )
RETURN
IF(_count > 0, "YES", "NO")