Forum Discussion

Nino_Birdie's avatar
Nino_Birdie
Frequent Visitor
6 years ago

Remove duplicates and blanks

Im trying to make a calculated table and i cant remove the duplicates, leaving blanks between data.

The query is the following:

 

DatosExtra = 
    DISTINCT(SELECTCOLUMNS(EstadosPostulantes;  
    "Etapas"; EstadosPostulantes[Etapa];
    "Exito";  if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 1 ;EstadosPostulantes[Id]); 
    "Fracaso"; if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 0 ;EstadosPostulantes[Id]))) <br /><br />

 

 

And the results are the following

 

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Use summarize and do min or max grouping

    DatosExtra = 
    summarize(EstadosPostulantes;  
    "Etapas"; EstadosPostulantes[Etapa];
    "Exito";  minx(EstadosPostulantes; if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 1 ;EstadosPostulantes[Id])); 
    "Fracaso"; minx(EstadosPostulantes; if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 0 ;EstadosPostulantes[Id]))
	)
	
OR
DatosExtra = 
    DISTINCT(summarize(EstadosPostulantes;  
    "Etapas"; EstadosPostulantes[Etapa];
    "Exito";  minx(EstadosPostulantes; if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 1 ;EstadosPostulantes[Id])); 
    "Fracaso"; minx(EstadosPostulantes; if(EstadosPostulantes[Etapa] = [Etapa] && EstadosPostulantes[Tipo] = 0 ;EstadosPostulantes[Id]))
	)
	)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • Nino_Birdie's avatar
      Nino_Birdie
      Frequent Visitor
      6 years ago

      amitchandakI tried both of your codes and they throw me the following

      A single value for column 'Etapa' in table 'EstadosPostulantes' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

       

      And if i apply minx or maxx inside EstadosPostulantes[Etapa] it only returns me one row.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        I took this from you if, what is the condition you want to put here. Yes , either they can column from same table or a column from the filter table and value like the other filter

        EstadosPostulantes[Etapa] = [Etapa]