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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wolfy_
Helper I
Helper I

Condition between two tables

Hi,

 

I have a challenge that I do not know from where to start.

 

I have two tables, on with the "work" to be done and the other with the actual state of the network.

As I know I do not have a way to do relationships between the two tables.

 

I only can start to work if the frequency(s) is(are) present, so I was thinking to have a column with a GO/NOGO status.

 

I have an example of tipical cases:

 

The table with the work to do is:

(I have added a column with observation to easily analysis and note that 2600 is diferent 2600p)

 

IDFreq observation
4055711800 1800 do not exist in actual, but exist 700 and 2100 ->NOGO
5390982100 2100 exist, also 700 and 2600 -> GO
5284622600P 2600p exist, also 700, 2100 and 2600 -> GO
165376700 700 exist in actual -> GO
422037700/1800 neider 700 or 1800 exist in actual -> NOGO
545474700/2100 700 exist in actual but 2100 do not exist -> NOGO
1018145700/2600 700 and 2600 exist in actual -> GO
351054700 do not exist in actual ->NOGO

 

 

The table with the real state of the network is:

IDFreq
405571700
405571700
405571700
4055712100
4055712100
4055712100
10181452600
1018145700
1018145700
1018145700
10181452600
10181452600
165376700
165376700
165376700
528462700
528462700
528462700
5284622600p
5284622600p
5284622600p
5284622600
5284622600
5284622600
5284622600
5284622600
5284622600
5284622100
5284622100
5284622100
5390982600
539098700
539098700
539098700
5390982600
5390982100
5390982100
5390982100
5390982600
545474700
545474700
545474700

 

 

Is there a way to solve this so I can have a column with GO to show if the work is ready to start?

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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 🙂

image.png

 

Hopefully, this is what you are looking for!

 

Regards and thank you for this interesting question,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hi Tom,

 

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

 

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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 🙂

image.png

 

Hopefully, this is what you are looking for!

 

Regards and thank you for this interesting question,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom,

 

Thanks for the quick answer.

 

I was trying to study and implement your solution, but there PowerBi give the following error:

"The arguments in GenerateSeries function cannot be blank."

 

This error is because the [Freq] is null in some ID's in my files?

 

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

Hi Tom,

 

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

 

Hey,
this should do the trick:

 

ToDo = 
if(isblank([Freq])
,"NOGO"
,
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")
)

This is not tested, but I think you get the idea.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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