duplicate values

5 Topics

Replacing duplicate values with 0
Hello, in the example below I am trying to visualize the amount of students (STUDENT_COUNT) as a percentage of the total group (GROUP_COUNT) for each distinct GROUP_BY_VALUE, however the GROUP_COUNT is duplicated for each distinct TEST_RESULT and GROUP_BY_VALUE combination. For example, in the first two rows, for Eng Prof students I want to display (118+386)/1267 not 2534. Any suggestions on how to replace the duplicate GROUP_COUNTS with zeros? Or another solution? Thanks!
KMEAGHER
2 years ago Place DAX Commands and Tips
525Views
0likes
1Comment
Need to Remove Duplicated Text String in Calculated Columns
Hello, I need to remove duplicate project names and I used the dax below (ignore red needed to remove identifying data). If you remove the Values It currently shows up as: Project: A, A, A, B, B,B,C,C,D,D,D What I want is: Project: A,B,C,D I thought I could get that with Values (See Below) at the end but it gives me "A table of multiple values was supplied where a single value was expected." I thought this would get me close @https://community.fabric.microsoft.com/t5/Desktop/To-remove-duplicate-char-from-string-on-each-row-using-DAX/m-p/2896296
spider_Monkey
2 years ago Place DAX Commands and Tips
1.7KViews
0likes
6Comments
Remove Duplicate Values using CONCATENATEX and Direct Query
I am having trouble getting values from a Direct Query to appear correctly in a table using the CONCATENATEX fxn. I have tried multiple fxn setups and all present varying issues. Each row corresponds to a specific ID and each Finding (bmpObs) only appears once in the SQL dataset for each ID. The SQL dataset looks similar to this: ID#1 - value1 ID#1 - NULL ID#1 - value2 ID#1 - value3 ID#1 - NULL ID#2 - value1 ID#2 - NULL ID#2 - value4 ID#2 - value6 ID#2 - NULL ID#3 - value2 ID#3 - NULL ID#3 - value4 ID#3 - value5 ID#3 - NULL I'm trying to get a result that looks like: ID#1 - value1, value2, value3 ID#2 - value1, value4, value6 ID#3 - value2, value4, value5 Here are the DAX formulas I've tried using, in addition to others I can't remember at the moment. 1. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(FindingsFacilityVisualList[bmpObs]) RETURN CONCATENATEX( DISTINCT( FILTER(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs] <> BLANK() ) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in duplicated values: ID#1 - value1, value2, value3, value1, value2, value3, value1, value2, value3 ID#2 - value1, value4, value6, value1, value4, value6, value1, value4, value6 ID#3 - value2, value4, value5, value2, value4, value5, value2, value4, value5 --------- 2. List of BMP observations = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('FindingsFacilityVisualList'[bmpObs]) RETURN CALCULATE(CONCATENATEX( DISTINCT( FindingsFacilityVisualList[bmpObs]), FindingsFacilityVisualList[bmpObs], ", " )) Results in a comma before the first value: ID #1 - , value1, value2, value3 ID #2 - , value1, value4, value6 ID #3 - , value2, value4, value5 --------- 3. List of BMP observations = CONCATENATEX( CALCULATETABLE( VALUES(FindingsFacilityVisualList[bmpObs]), ALLEXCEPT(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs]), NOT ISBLANK(FindingsFacilityVisualList[bmpObs]) ), FindingsFacilityVisualList[bmpObs], ", " ) Results in incorrect values: ID#1 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#2 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#3 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#4 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 ID#5 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6 *ID#4 & #5 should not have values associated with them --------- 4. List of BMP observations = CONCATENATEX ( FILTER ( FindingsFacilityVisualList, LEN ( FindingsFacilityVisualList[bmpObs] ) > 0 ), FindingsFacilityVisualList[bmpObs], ", " ) Results in Error: Can't display the visual. --------- Any suggestions for alternate ways to get to my desired end result?
Solved
HoppeG76
2 years ago Place DAX Commands and Tips
3.4KViews
0likes
7Comments
Calculation Involving Duplicates
Without deleting duplicates, how do I write a formula to calculate the total scrap percentage by date? Scrap% = (Machine Scrap/(Machine Scrap + Good Quantity)) * 100 Production Day Machine Scrap Good Quantity 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589
Anonymous
3 years ago Place DAX Commands and Tips
1.8KViews
0likes
9Comments
RankX Ranking same values as same rank
Good Day, I am getting duplicate rankings when they have the same value. In this case its zeros. Is there a way to get it to represent 1,2 and 3 instead of 1,1,1? Pro Audit Rank = RANKX(ALL(System_Users),Audit[SUM Pro Audit])
Anonymous
3 years ago Place DAX Commands and Tips
1.5KViews
0likes
1Comment