Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
ID | Freq | observation | |
405571 | 1800 | 1800 do not exist in actual, but exist 700 and 2100 ->NOGO | |
539098 | 2100 | 2100 exist, also 700 and 2600 -> GO | |
528462 | 2600P | 2600p exist, also 700, 2100 and 2600 -> GO | |
165376 | 700 | 700 exist in actual -> GO | |
422037 | 700/1800 | neider 700 or 1800 exist in actual -> NOGO | |
545474 | 700/2100 | 700 exist in actual but 2100 do not exist -> NOGO | |
1018145 | 700/2600 | 700 and 2600 exist in actual -> GO | |
351054 | 700 | do not exist in actual ->NOGO |
The table with the real state of the network is:
ID | Freq |
405571 | 700 |
405571 | 700 |
405571 | 700 |
405571 | 2100 |
405571 | 2100 |
405571 | 2100 |
1018145 | 2600 |
1018145 | 700 |
1018145 | 700 |
1018145 | 700 |
1018145 | 2600 |
1018145 | 2600 |
165376 | 700 |
165376 | 700 |
165376 | 700 |
528462 | 700 |
528462 | 700 |
528462 | 700 |
528462 | 2600p |
528462 | 2600p |
528462 | 2600p |
528462 | 2600 |
528462 | 2600 |
528462 | 2600 |
528462 | 2600 |
528462 | 2600 |
528462 | 2600 |
528462 | 2100 |
528462 | 2100 |
528462 | 2100 |
539098 | 2600 |
539098 | 700 |
539098 | 700 |
539098 | 700 |
539098 | 2600 |
539098 | 2100 |
539098 | 2100 |
539098 | 2100 |
539098 | 2600 |
545474 | 700 |
545474 | 700 |
545474 | 700 |
Is there a way to solve this so I can have a column with GO to show if the work is ready to start?
Solved! Go to Solution.
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".
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,
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
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |