Forum Discussion

hejszyszky's avatar
hejszyszky
Helper II
4 years ago
Solved

Exclude multiple values from calculated column

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!

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      hejszyszky
      Helper II
      4 years ago

      Hey amitchandak ,

       

      Thanks for quick response.

       

      Output of measure above is ; 

      DateNameNiezaraportowany czasC
      1/3/2021LSN01   330
      1/3/2021LSN01   330
      1/3/2021LSN01   330
      1/4/2021LSN01   508
      1/4/2021LSN01   508
      1/4/2021LSN01   508

       

      My desired output would be:

      DateNameNiezaraportowany czasC
      1/3/2021LSN01   330
      1/3/2021LSN01   0
      1/3/2021LSN01   0
      1/4/2021LSN01   508
      1/4/2021LSN01   0
      1/4/2021LSN01   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 🙂 ?

       

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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's avatar
      hejszyszky
      Helper II
      4 years ago

      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's avatar
        vojtechsima
        Super User
        4 years ago

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

        However, it really helps with your issue.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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