Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nino_Birdie
Frequent Visitor

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

 

2020-01-30 13_11_28-Workplaz v2 - Power BI Desktop.png

 

5 REPLIES 5
amitchandak
Super User
Super User

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

@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.

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]

@amitchandak  meanwhile i have created a mockup of what i expected, and also new columns Called NExito and NFracaso, the following query :

DatosExtra = 
   UNION(
       ROW("Etapa";1;"Exito"; 1;"Fracaso";"";"NExito";"a";"NFracaso";"");
        ROW("Etapa";2;"Exito";3;"Fracaso";2;"NExito";"c";"NFracaso";"b");
        ROW("Etapa";3;"Exito";5;"Fracaso";4;"NExito";"e";"NFracaso";"d");
        ROW("Etapa";4;"Exito";7;"Fracaso";6;"NExito";"f";"NFracaso";"g");
        ROW("Etapa";5;"Exito";9;"Fracaso";8;"NExito";"i";"NFracaso";"h");
        ROW("Etapa";6;"Exito";11;"Fracaso";10;"NExito";"k";"NFracaso";"j");
        ROW("Etapa";7;"Exito";13;"Fracaso";12;"NExito";"m";"NFracaso";"l")
       )

 

Returns the following (what i expected):

 

2020-01-31 11_10_52-Window.png

 

So, is there something i can do to replicate that? By making rows or columns?

Up. I still cant figure how to do what i need 😐

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.