Forum Discussion

KH_Mike's avatar
KH_Mike
Helper III
5 years ago
Solved

Calculate Column - Check Row Record

Hi All,   I have a data like below table. Now, I would like to create a new column as Slicer for easy comparsion. Is it possible to create under below condition? If not, any other way to acheive th...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, KH_Mike 

    Try to create a calculated column like below:

    _Col2 =
VAR _Int =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Global"
        ),
        [Year/Month]
    )
VAR _Glo =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Internal"
        ),
        [Year/Month]
    )
RETURN
    IF (
        'Table'[Actual/ Forecast] = "Foecast",
        "N",
        IF ( 'Table'[Year/Month] IN _Glo && 'Table'[Year/Month] IN _Int, "Y", "N" )
    )

    Result:

     

    Hope this helps

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, KH_Mike 

Please check the below.

It is for creating a new column.

 

 

New Column CC =
VAR currentyearmonth = 'Table'[YearMonth]
VAR currentdescription = 'Table'[Actual / Forecast]
VAR conditions =
COUNTROWS (
FILTER (
'Table',
'Table'[Actual / Forecast] <> currentdescription
&& 'Table'[YearMonth] = currentyearmonth
)
)
RETURN
IF ( conditions = 1, "Y", "N" )

 

https://www.dropbox.com/s/45iqt5e2owswkee/khmike.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

KH_Mike's avatar
KH_Mike
Helper III
5 years ago

Hi Jihwan_Kim ,

 

I can't perform the same on my dateset. I guess the reason maybe my dateset have duplication on "YearMonth". For the same "YearMonth", I have serval rows of records contain different data (e.g.: Sales Name, Customer Name ...etc.). Do you have any ideas to handle this situation? Thank you.

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, KH_Mike 

    Thank you for your feedback.

    Sorry that I quite do not understand your last question.

    Please let me know how your expected result looks like.

     

    Thanks.

    • KH_Mike's avatar
      KH_Mike
      Helper III
      5 years ago

      Hi Jihwan_Kim 

       

       

      What I want to show is like below. I try to use your DAX but can't show correctly.

      The logic is like this.

       

      If [Actual / Forecast] = "Forecast" then "N"

      If [Actual / Forecast] = "Actual" and both Internal & Global under [Internal / Global] contain same [YearMonth] then "Y" else "N"