Exclude multiple values from calculated column

[hejszyszky]

Hi, 
Given code and output below, id like to exclude multiple values based on date and name.

IF I have more than one row of same Date&Name id like to output calculation (330) only in one row. 

So basically output below in last column would change to 330, 0, 0, 508, 0, 0.

Is there a way to do that? >> I know measure would solve that, but i need to include these values by day in graph so there is a need to do that by calculated col.

Thanks in advance!

[v-luwang-msft · Accepted solution]

Hi @hejszyszky ,

Since I do not have your specific data, I have created a template data based on what you have provided so far:

Then use the below dax to create a new column:

final = 
VAR index11 =
    RANKX (
        FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
        'Summarized Raport'[Zmiana(A,B,C,D)],
        ,
        ASC
    )
RETURN
    IF (
        index11 = 1,
        SUMX (
            FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
            'Summarized Raport'[value]
        ),
        0
    )

Final output:

You can adapt my code to your specific environment and needs.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[vojtechsima]

Hi, @hejszyszky 
I believe I get your point, here's the picture and code:

DuplicityCheck = 
var currentDate = 'Table'[Date]
var currentName = 'Table'[Name]
var currentIndex = 'Table'[Index]
var duplicityvalues = filter('Table', currentDate = 'Table'[Date] && currentName = 'Table'[Name])
var duplicity = COUNTROWS(duplicityvalues)
var LowestDuplicity = MINX(duplicityvalues, 'Table'[Index])
var _removeDuplicate = SWITCH(TRUE(),
    LowestDuplicity = currentIndex &&  duplicity >=2 , "330",
    duplicity = 1, "Your expression",
    "0")

return _removeDuplicate

Please note that "your expression" represents an expression that you would normally use if everything is correct.

[hejszyszky]

Hi, @vojtechsima 
Thanks for your fast reply!
I am facing some issues, i believe its with Index column. In your scenario,Index column, is it just a row number?

[vojtechsima]

Hi, @hejszyszky 
It's just a simple Index column generated by Power Query:

However, it really helps with your issue.

[hejszyszky]

Well thats unlucky, cuz my table is an dax expression 😐

[amitchandak]

@hejszyszky ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[hejszyszky]

Hey @amitchandak ,

Thanks for quick response.

Output of measure above is ; 

Date	Name	Niezaraportowany czasC
1/3/2021	LSN01	330
1/3/2021	LSN01	330
1/3/2021	LSN01	330
1/4/2021	LSN01	508
1/4/2021	LSN01	508
1/4/2021	LSN01	508

My desired output would be:

Date	Name	Niezaraportowany czasC
1/3/2021	LSN01	330
1/3/2021	LSN01	0
1/3/2021	LSN01	0
1/4/2021	LSN01	508
1/4/2021	LSN01	0
1/4/2021	LSN01	0

Zeroes in desired output are because there is a repetition in one date and name

In conclusion,  if in any same date and same name there is more than one row, include only one value.

Am i clear here 🙂 ?