Forum Discussion

PierreCA's avatar
PierreCA
Icon for Resolver I rankResolver 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, ...
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))



     

PierreCA's avatar
PierreCA
Icon for Resolver I rankResolver 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
Icon for Resolver I rankResolver 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))