Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power Bi community, I am running into the following problem and I'd like some help to solve this issue.
We're working with DirectQueries and we'd like to remove the duplicated values and leave the ones with the value "accepted" in the status column.
My table can be resumed to:
ID, Status
We currently record two entries if a student signed up and was accepted to the school program. That means, if "John" signed up to the program and got accepted he would be recorded in our table as follows:
John,signedup
John,accepted
Mary,signedup
That means that John signedup and got accpeted but Mary did not got accepted, sorry Mary.
Is there a way to remove duplicates and leave just the entries with the accepted status?
Thanks
Solved! Go to Solution.
Drag measure below to Visual level filters and set Show items when the value is 1.
Measure =
IF (
CALCULATE ( DISTINCTCOUNT ( Table1[Status] ), ALLSELECTED ( Table1[Status] ) ) = 1
|| MAX ( Table1[Status] ) = "accepted",
1
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix,
Thank you for taking your time to comment on my thread.
Keeping the entries is a must that are not duplicated is a most, after all, the goal is to remove the duplicates leaving only those who are not duplicated and only one entry for those with the status as "accepted".
In regards to your solution, I tried it in the Query Editor and the result was not the right for us, that means, it ended up removing all the other columns (there's more than just id and status, I just summarized the table to simplify the question). Is there a workaround to this or are we applying your solution the wrong way?
Hi @Achartuni ,
Im assuming that you made the group by option correct?
In this case you need to place all the columns you need to choose advance, that will allow you to add more columns to your group by and also aggregations.
You need to select all columns that have similar values to the Groupings and all columns that have different values were you want to keep only the last one in the Aggregation.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
That's correct, we tried that option.
Sadly, since we're using DirectQuery, this workaround isn't allowed by PowerBI when working with such connection to the DB.
Any other suggestion would be nice.
Thank you
Drag measure below to Visual level filters and set Show items when the value is 1.
Measure =
IF (
CALCULATE ( DISTINCTCOUNT ( Table1[Status] ), ALLSELECTED ( Table1[Status] ) ) = 1
|| MAX ( Table1[Status] ) = "accepted",
1
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |