Discussion :

[Mirann]

Bonjour,

Je suis débutante sur Power BI et je bloque actuellement sur un problème depuis quelques jours : j'ai 2 tables, une que j'utilise comme référence et une autre "active".



Je cherche donc à reproduire la fonction SOMMEPROD sur Power BI.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(SOMMEPROD(((F3=$B$3:$B$10)*1)*($A$3:$A$10&""=STXT(E3;2;1))*(G3=$C$3:$C$10));"OK";"NO")

J'ai cherché un peu partout et on me suggérait SUMX mais ça n'avait pas l'air de fonctionner. J'ai également essayé les colonnes conditionnelles mais il n'est pas possible de le faire entre 2 tables. Je bloque également sur la fonction STXT. Avez-vous une idée ?

J'ai également une seconde question : dans le cas où la formule fonctionnerait, est-il plus pertinent de créer une nouvelle table avec chaque "OK" ou "NO" pour chaque ligne, ou une simple mesure suffirait et choisir "Table" en visualisation me donnerait le même résultat ? Ce qui prendrait moins d'espace que de créer une vraie table. Je ne sais pas si je suis assez claire...

Je joins également le fichier version Excel au cas où.

Je vous remercie d'avance

[Tippa]

Bonjour,

Première question, souhaitez-vous faire ce calcul côté Power Query ou en DAX côté Desktop.
Par exemple si vous cherchez un texte dans une chaine de texte (STXT), en Query vous avez un Text.Contains, Text.Middle, Text.Range...
, et en DAX des fonctions comme MID, EXACT, SEARCH...
Plus d'infos ici pour DAX et ici pour M.

Pour le sommeprod, il n'y a pas d'équivalent direct mais surement des solutions alternatives qui dépendent bien sur du résultat souhaité.

Bref n'hésitez pas à dire plus pour pouvoir vous aider au mieux...

Bonne journée

[Mirann]

Merci pour la réponse !

J'espère pouvoir faire passer tous mes calculs sur Power BI pour pouvoir être indépendante d'Excel et travailler à 100% sur Power BI.

Pour le SOMMEPROD, je cherche à faire en sorte que si (condition A) ET (condition B) ET (condition C) sont respectées, alors c'est "OK" pour la ligne, et ce, pour toutes les lignes de ma table "active". Dans l'extrait j'ai mis 4 lignes mais mon fichier possède plus de 7000 lignes.
Les conditions reprennent les colonnes de la table de référence et c'est ce que je n'arrive pas à trouver.

Pour ce qui est de DAX ou M, je vous avoue qu'étant débutante je ne saurai pas trop lequel est le plus adapté. Plutôt DAX pour la modélisation des données ?

En résultat je cherche à pouvoir avoir un filtre "OK" ou "NO" pour savoir les lignes correspondants. Par exemple, je saurai que les lignes 546, 876 et 989 ne respectent pas les 3 conditions itératifs.

J'essaie de m'expliquer au mieux, désolée si ce n'est pas claire

[Tippa]

Re,

Si on part sur du DAX, l'alternative à votre SOMMEPROD qui fait un test, serait :
FILTER([TableAFiltrer];conditionA&&conditionB&&conditionC)
Qui vous retournera une table avec les résultats OK seulement.

Le && permet d'exprimer le ET dans les conditions et || (Alt Gr 6) permet d'exprimer le OU.

On peut aussi utiliser un IF ou un SWITCH pour le calcul conditionnel.
IF(MID([Truc];1;2)="bidule";"OK";"KO").
Ce IF ou ce SWITCH sera plus facile à écrire dans une colonne que dans une mesure (question de contexte de ligne), donc je vous recommande de le faire en DAX dans une colonne.

Si vous optez pour la première solution (avec Filter), comme le résultat est une table il faudra utiliser le bouton "Nouvelle Table" et pas "Nouvelle Colonne" ou "Nouvelle Mesure".
Pour caricaturer les choses un peu, on peut dire que les "Nouvelle Colonne" sont idéales pour le calcul ligne par ligne
et "Nouvelle Mesure" lorsque je calcule avec plusieurs valeurs d'une même colonne.
C'est un peu grossier comme définition mais cela vous donne une première piste...

J'espère que cela vous aide un peu...

[Mirann]

Merci beaucoup pour votre réponse précise ! Cela m'aide beaucoup à mieux comprendre Power BI

Le problème avec FILTER et IF c'est qu'ils ne prennent en compte qu'une table. Si je veux faire en sorte que TableActive[col A] = TableRéf[col B] comment est-ce possible ? J'aimerai pouvoir garder ces deux tables distinctes et ne pas simplement les fusionner.

[Tippa]

Merci beaucoup pour votre réponse précise ! Cela m'aide beaucoup à mieux comprendre Power BI

Le problème avec FILTER et IF c'est qu'ils ne prennent en compte qu'une table. Si je veux faire en sorte que TableActive[col A] = TableRéf[col B] comment est-ce possible ? J'aimerai pouvoir garder ces deux tables distinctes et ne pas simplement les fusionner.

Il est aussi possible de fusionner dans une nouvelle table... Si ça peut aider.
En DAX il y a aussi des LOOKUPVALUE, bref pas mal de solutions selon le résultat souhaité...
Mais là il est un peu tard ;-)

[Tippa]

Re,

Pour comparer tes deux valeurs, je vois deux pistes :

1/ Power Query : en fusionnant les 2 requêtes (fusion comme nouvelles, si tu souhaites aussi les avoir séparément), et ainsi tu n'as plus qu'une table. La comparaison devient simple.
TabA[Col1]=TabA[col2]

2/ Desktop : Tu gardes les tables séparées, tu les relies dans la vue modèle et tu compares avec un RELATED() TabA[Col]=RELATED(TabB[col]).

J'espère que cela aide

[Mirann]

Bonjour,

J'ai utilisé LOOKUPVALUE vu que j'ai plusieurs conditions et ça a l'air de bien fonctionner merci ! Maintenant le problème est que j'essaie de rajouter une 3ème condition avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

IF(MID([Truc];1;2)="bidule";"OK";"KO").

pour faire en sorte d'avoir SI TabA[colA]=TabB[colB] + TabA[colB]=TabB[colC] + TabA[2ème caractère de colC]=TabB[colA].

En tout cas merci encore pour ton aide précieuse !

[Tippa]

Ce devrait être possible en plusieurs lignes avec quelque chose comme...

SI(TabA[colA]=TabB[colB] + TabA[colB]&&TabB[colC] + TabA[2ème caractère de colC]=TabB[colA] (le && pour le ET, si besoin || pour un OU)

[danixdb]

Bonjour Mirann, Le Forum,

Voilà, comment je ferais, mais il y certainement d'autres solutions

1 - Méthode Power query
a) Création d'une requête sur base de la table de référence, comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
let
 Source = Excel.CurrentWorkbook(){[Name="tReference"]}[Content],
 #"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne1", type any}, {"TABLE REFERENCE", type text}, {"Colonne2", type any}}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Type modifié", [PromoteAllScalars=true]),
    #"Type modifié1" = Table.TransformColumnTypes(#"En-têtes promus",{{"VAL", Int64.Type}, {"LL", type text}, {"REF", Int64.Type}})
in
    #"Type modifié1"

b) Création d'une requête sur base de la table active (tableau structuré nommé tActive), comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
let
    Source = Excel.CurrentWorkbook(){[Name="tActive"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne1", type text}, {"TABLE ACTIVE", type text}, {"Colonne2", type any}}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Type modifié", [PromoteAllScalars=true]),
    #"Personnalisée ajoutée" = Table.AddColumn(#"En-têtes promus", "VAL", each Text.Middle([ID],1,1)),
    #"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"REF", Int64.Type}, {"VAL", Int64.Type}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié1", {"LL", "VAL"}, tReference, {"LL", "VAL"}, "tReference", JoinKind.LeftOuter),
    #"tReference développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "tReference", {"REF"}, {"REF.1"}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"tReference développé", "Comparaison", each if [REF]=[REF.1] then "OK" else "NOK"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"VAL", "REF.1"})
in
    #"Colonnes supprimées"

Dans cette requête, on a créé une colonne personnalisée nommée VAL qui prend le deuxième caractère de la chaîne ID. On fait une jointure entre les deux requêtes afin de rapatrier le champ REF de la table de référence. Ajout d'une colonne personnalisé nommée Comparaison afin de pouvoir comparer les deux colonnes REF (active) et REF.1 (référence) si les deux colonnes sont identiques on met "OK" sinon "NOK", puis on supprime les ddeux colonnes VAL et REF.1 et on charge le tableau dans Excel ou Power BI.

2 – Méthode DAX (Power BI)
Création des deux requêtes, 1 pour chaque table, comme suit :
Requête Référence

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\OneDrive\Mirann.xlsx"), null, true),
    tReference_Table = Source{[Item="tReference",Kind="Table"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(tReference_Table, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"REF", Int64.Type}})
in
    #"Type modifié"

Requête Active

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\OneDrive\Mirann.xlsx"), null, true),
    tActive_Table = Source{[Item="tActive",Kind="Table"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(tActive_Table, [PromoteAllScalars=true]),
    #"Personnalisée ajoutée" = Table.AddColumn(#"En-têtes promus", "VAL", each Text.Middle([ID],1,1)),
    #"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"REF", Int64.Type}, {"VAL", Int64.Type}})
in
    #"Type modifié"

Création colonne Concat dans chaque table (sur les colonnes LL et VAL), ensuite on établira une relation entre les deux tables sur la colonne Concat et il ne restera plus qu'à créer la colonne Comparaison avec la formule suivante :
Comparaison = if(related(tReference[REF])=tActive[REF],"OK","NOK")

En espérant que cela te donnera des pistes.
Cordialement
DanixDB

[Massinissa78]

Bonjour,
Solution avec DAX:
Pour rajouter une colonne "OK","NO" à la table Active

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Test = 
VAR REF = 'TABLE ACTIVE'[REF]
VAR REFcolonne =
    LOOKUPVALUE ( 'TABLE REFERENCE'[REF], 'TABLE ACTIVE'[LL], 'TABLE ACTIVE'[LL] )
RETURN
    SWITCH ( TRUE (), REFcolonne = REF, "OK", "NO" )

Pour créer une mesure "OK","NO":

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Mesure = 
VAR tble =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                'TABLE ACTIVE',
                'TABLE ACTIVE'[ID],
                'TABLE ACTIVE'[REF],
                'TABLE ACTIVE'[LL]
            ),
            "REFcolonne", LOOKUPVALUE ( 'TABLE REFERENCE'[REF], 'TABLE ACTIVE'[LL], 'TABLE ACTIVE'[LL] )
        ),
        "@test", IF ( [REFcolonne] = 'TABLE ACTIVE'[REF], "OK", "NO" )
    )
RETURN
    IF (
        HASONEVALUE ( 'TABLE ACTIVE'[REF] ),
        CONCATENATEX ( tble, [@test], BLANK () )
    )

[Mirann]

Bonjour,

Merci pour vos réponses, à vrai dire je suis toujours dessus.
J'ai créé une colonne calculée avec LOOKUPVALUES mais je me demande si une mesure n'est pas mieux ?

J'ai essayé ta méthode Massinissa78 mais ça me renvoie un vide quand j'essaie de le visualiser sous format tableau.
Lors du code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"REFcolonne", LOOKUPVALUE ( 'TABLE REFERENCE'[REF], 'TABLE ACTIVE'[LL], 'TABLE ACTIVE'[LL] )

, DAX me propose la TABLE REF et non la table active. Est-ce lié à ça ?

[Massinissa78]

Bonjour,
est ce que les deux tables sont liées dans le modèl?
sinon je mets le pbix pour voir s'il y a des differences .
les deux tables sont liées par les colonnes [LL] dans le model

[Mirann]

Oui les deux tables sont liées, j'ai essayé avec [LL] ou [REF] mais ça reste la même.
Par contre je n'arrive pas à ouvrir ton fichier sous .rar

[Massinissa78]

j'ai mis le fichier sous .zip

[Mirann]

Merci, j'ai finalement réussi avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Test = IF(
    ISBLANK(
        LOOKUPVALUE('Table 1'[Col4],
        'Table 1'[Col1], MAX('Table 2'[Col 1]),
        'Table 1'[Col2], MAX('Table 2'[Col 2]),
        'Table 1'[Col3], MAX('Table 2'[Col 3]),
        'Table 1'[Col4], MAX('Table 2'[Col 4]),
        0,
        1)

[kalz75]

@Marrina est-il possible de voir comment vous avez fait la syntaxe DAX qui est équivalent à SOMMEPROD sous Excel SVP?

Merci d'avance.