Condition between two tables

Hey,

I created a calculated column in your first table, I call that table "Todo", this is the DAX of the calculated column:

var thisID = 'ToDo'[ID]
var thisFreq = 'ToDo'[Freq]
var thisFreqPath = SUBSTITUTE(thisFreq,"/","|")
var thisFFreqPathLength = PATHLENGTH(thisFreqPath)
var thisFreqAsTable =
    SELECTCOLUMNS(  
        ADDCOLUMNS(
            GENERATESERIES(1,thisFFreqPathLength,1)
            ,"Freq", PATHITEM(thisFreqPath,''[Value],TEXT)
        )
        ,"Freq", [Freq]
    )
var thatFrequencies = 
    CALCULATETABLE(
        VALUES(Observations[Freq])
        ,FILTER(
            ALL('Observations')
            ,'Observations'[ID] = thisID
        )
    )
return
IF(COUNTROWS(INTERSECT(thisFreqAsTable,thatFrequencies)) = COUNTROWS(thisFreqAsTable), "GO", "NOGO")

The thinking behind this is as follows, due to the alphanumeric naming of a Freq, namely 2600P, ordering is difficult, and due to this string comparison almost impossible. For this reason, the solution is based on table comparison using INTERSECT(...)  and COUNTROWS

This may look weird, but it's also a complex query :-)

Basically, I create two tables, the one from the observations table is simple, I use VALUES(...) and FILTER(..., 'Observation'[ID] = thisID)) to create a one-column table that contains the distinct values.

The creation of the table that contains the freq from the todo table is more complex. The slash "/" is substituted by "|", this transforms the string thisFreqPath into a Path. Now with some little DAX tricks, this Path can be transformed into a table.

Then, finally, some simple comparisons, and we're done :-)

Hopefully, this is what you are looking for!

Regards and thank you for this interesting question,

Tom

Hi Tom,

This solution worked for me, I replaced all null values for "0".