Forum Discussion

PierreCA's avatar
PierreCA
Resolver I
2 years ago
Solved

Calculate MINX, receive only half answer

I'm trying to get the most ancien date of a serie status related to a demand created by an entreprise. But, it result that I only get half of the answer. 

16 = opening
15= closing

in between, many other kind of satus. 

 

Three tables are involved. The backend are Sharepoint lists. 

 

VAR datePlusAncienne = 
CALCULATE(
    IF(15 in VALUES('EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]),
        BLANK(), 
         MINX(FILTER('EvolutionStatutDemande2-Test', [FK_idStatutDemandeId]=16), [dateStatut])
        ),RELATEDTABLE( Entite))

Red lines represent missing dates. I know the missing info. I try whithout RELATEDTABLE, but it returns the same result. 

 

What's missing in the formula?

Need Help
Tips and Tricks
  • PierreCA's avatar
    PierreCA
    2 years ago

    Solution

    The solution is to use SUMMARIZE and INTERSECT to get a demand both Open and Closed. For then find demands only Open. 

    VAR ID_DemandesStatutOuvertes = 
    CALCULATETABLE( 
    SUMMARIZE('EvolutionStatutDemande2-Test','EvolutionStatutDemande2-Test'[f_idDemandeId]),
    'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=16,RELATEDTABLE( Entite)
    )
    

VAR ID_DemandesFermees =
    CALCULATETABLE( 
    SUMMARIZE('EvolutionStatutDemande2-Test','EvolutionStatutDemande2-Test'[f_idDemandeId]),
    'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=15 ,RELATEDTABLE( Entite)
    )

// Liste des f_idDemandeId qui sont à la fois ouvertes et fermées
VAR intersectionTables = INTERSECT( ID_DemandesStatutOuvertes , ID_DemandesFermees)


VAR DateDemandePlusAncienneActive = 
    CALCULATE(
        MIN('EvolutionStatutDemande2-Test'[dateStatut]), FILTER('EvolutionStatutDemande2-Test',
        'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=16), NOT
        'EvolutionStatutDemande2-Test'[f_idDemandeId] IN intersectionTables,RELATEDTABLE( Entite))



     

3 Replies

    • PierreCA's avatar
      PierreCA
      Resolver I
      2 years ago

      Hello amitchandak ,
      relatedtable       role is to regroup demands by related  Entite. But I get the same result without it. No data is copied from table to another. 

      Table Entite Demands EvolutionStatutDemand 
      ID EntiteID DemandID EvolutionStatutDemandID 
      Foreign Key   FK_EntiteID FK_DemandID 

       

      • PierreCA's avatar
        PierreCA
        Resolver I
        2 years ago

        Solution

        The solution is to use SUMMARIZE and INTERSECT to get a demand both Open and Closed. For then find demands only Open. 

        VAR ID_DemandesStatutOuvertes = 
    CALCULATETABLE( 
    SUMMARIZE('EvolutionStatutDemande2-Test','EvolutionStatutDemande2-Test'[f_idDemandeId]),
    'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=16,RELATEDTABLE( Entite)
    )
    

VAR ID_DemandesFermees =
    CALCULATETABLE( 
    SUMMARIZE('EvolutionStatutDemande2-Test','EvolutionStatutDemande2-Test'[f_idDemandeId]),
    'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=15 ,RELATEDTABLE( Entite)
    )

// Liste des f_idDemandeId qui sont à la fois ouvertes et fermées
VAR intersectionTables = INTERSECT( ID_DemandesStatutOuvertes , ID_DemandesFermees)


VAR DateDemandePlusAncienneActive = 
    CALCULATE(
        MIN('EvolutionStatutDemande2-Test'[dateStatut]), FILTER('EvolutionStatutDemande2-Test',
        'EvolutionStatutDemande2-Test'[FK_idStatutDemandeId]=16), NOT
        'EvolutionStatutDemande2-Test'[f_idDemandeId] IN intersectionTables,RELATEDTABLE( Entite))