Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
KH_Mike
Helper III
Helper III

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 the have the same result?

 

Show "Y" only if "Acutal" and "Forecast" both contain the same "YearMonth" record

 

Actual / ForecastYearMonthNew Column
Actual202101Y
Actual202102Y
Actual202103Y
Actual202104Y
Actual202105Y
Forecast202101Y
Forecast202102Y
Forecast202103Y
Forecast202104Y
Forecast202105Y
Forecast202106N
Forecast202107N
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

v-angzheng-msft_0-1622616599044.png

 

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.

View solution in original post

8 REPLIES 8
v-angzheng-msft
Community Support
Community Support

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:

v-angzheng-msft_0-1622616599044.png

 

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.

v-angzheng-msft
Community Support
Community Support

Hi, @KH_Mike 

 

Not very clear for me,  why rows 8 to 13 are N

v-angzheng-msft_0-1622530945907.png

 

Hi @v-angzheng-msft 

 

Sorry for my unclear request. The logic should be 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"

 

KH_Mike
Helper III
Helper III

Hi All,

 

Anyone can help me? Thank you.

Jihwan_Kim
Super User
Super User

Hi, @KH_Mike 

Please check the below.

It is for creating a new column.

 

Picture2.png

 

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

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


Go to My LinkedIn Page


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.

 

Untitled.png

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.

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


Go to My LinkedIn Page


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"

 

Untitled.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.