compare a dynamic variable to a column in DAX

[Anonymous]

Hi there,

I am having difficulty with my need for a Power Bi report.
Here is the requirement:
I have 2 different source files. One us which serves as our repository (File A) and the other as data to import from this repository (File B).
Condition 1: The goal is to retrieve the value one by one of the line code of file B and compare it to the entire line code table of file A and do this for all the lines of file B.

Condition 2: I would have to compare the PK of file B to an interval of data that will be retrieved in file A with respect to the line code chosen in the first condition

If these 2 conditions are met, then the ambition value of file A is retrieved and placed in file B.

I managed to develop this using VBA but I would like to know if it was possible to transcribe it into power BI?

here is the code in vba

Sub Ambition()
Dim Pk_routeur As Double
Dim Code_ligne_routeur As String

Dim ligne_Routeur_IP As Integer
Dim ligne_Seg As Integer

Dim derniereLigne_rout As Integer
Dim derniereLigne_seg As Integer

Dim Ambition As String

Sheets("Routeur IP").Select
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("O1").Select
ActiveCell.FormulaR1C1 = "Ambition"
Range("O3").Select
derniereLigne_rout = Cells(Rows.Count, 1).End(xlUp).Row 'on selectionne notre onglet routeur et on enregistre la dernière ligne

Sheets("Segments_réseaux").Select 'on retourne dans l'onglet des segment et on stock la dernière ligne
derniereLigne_seg = Cells(Rows.Count, 1).End(xlUp).Row

For ligne_Routeur_IP = 2 To derniereLigne_rout 'on parcourt toutes les lignes du tableau routeur IP

Code_ligne_routeur = Worksheets("Routeur IP").Range("L" & ligne_Routeur_IP).Value 'on stock les 2 variables code ligne et pk du routeur
Pk_routeur = Worksheets("Routeur IP").Range("N" & ligne_Routeur_IP).Value

For ligne_Seg = 2 To derniereLigne_seg 'on parcourt toutes les lignes du tableau segmentation

If (Worksheets("Segments_réseaux").Range("H" & ligne_Seg).Value = Code_ligne_routeur) Then 'si le code ligne reccuperé dans l'onglet routeur ip = à celui de l'onglet segment

'la valeur du pk du routeur est compris dans l'interval pk debut et pk fin
If ((Worksheets("Segments_réseaux").Range("D" & ligne_Seg).Value <= Pk_routeur) And (Worksheets("Segments_réseaux").Range("E" & ligne_Seg).Value >= Pk_routeur)) Then

'on réccupère la valeur ambition et on la stock dans la colonne ambition de l'onglet routeur
Worksheets("Routeur IP").Range("O" & ligne_Routeur_IP) = Worksheets("Segments_réseaux").Range("AS" & ligne_Seg).Value
End If

End If

Next ligne_Seg

Next ligne_Routeur_IP

End Sub

[Anonymous · Accepted solution]

hello thanks you, 

I find the solution : 

Ambitions finales =

VAR Pk= 'Routeur IP'[PK]

VAR Ligne='Routeur IP'[Code ligne]

return

CALCULATE(CONCATENATEX(VALUES('Liste segments'[ambition_lbl]),'Liste segments'[ambition_lbl],"-"),FILTER(ALL('Liste segments'),'Liste segments'[lig_rg]= Ligne && 'Liste segments'[pk_dec_d]<= Pk && 'Liste segments'[pk_dec_f]>= Pk))

[Anonymous]

Hi @Anonymous ,

Based on your description, you want to look for the same data in Table A in table B and compare their order, right? I have a question about this, as I understand that A and B contain the same data, why do we need to say that the input in A is put into B?

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.