Forum Discussion

Jidnyasa2904's avatar
Jidnyasa2904
Icon for Helper I rankHelper I
2 years ago

Display rows in Metrics if comparable (Has value in both the columns)

Hi All, 

I have two measures:
This year = Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2024))
Last year = Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2023))
This gives me the result shown in Fig 1. 

Fig 1. 


I am looking for result shown in Fig 2. 
Fig 2. 


Display only the values if they are comparable. 
Appreciate it!

 

 



Need Help

7 Replies

  • dharmendars007's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hello Jidnyasa2904 , 

     

    Can you please try this measure...

     

    Comparable Value This Year =
    IF(
    NOT(ISBLANK(Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2024)))) &&
    NOT(ISBLANK(Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2023)))),
    Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2024)),
    BLANK())

    Comparable Value Last Year =
    IF(
    NOT(ISBLANK(Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2024)))) &&
    NOT(ISBLANK(Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2023)))),
    Calculate(Sum('table'[value]), Filter('Date Table', 'Date Table'[Year] = 2023)),
    BLANK())

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • Jidnyasa2904's avatar
      Jidnyasa2904
      Icon for Helper I rankHelper I
      2 years ago

      I tried using this formula. It does take off the rows that are blank from both columns but the total doesn't show up correct. 

       




      • dharmendars007's avatar
        dharmendars007
        Icon for Memorable Member rankMemorable Member
        2 years ago

        You can handle the totals with sumx or hasonevalue

         

        Thanks

        Dharmendar S

    • Jidnyasa2904's avatar
      Jidnyasa2904
      Icon for Helper I rankHelper I
      2 years ago

      If I use the same formula it throws an error - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

  • muhammad_786_1's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi Jidnyasa2904 

     

    Thanks for the quick reply from dharmendars007, please allow me to provide another insight:

     

    You can use this measure also and apply a filter for the measure Comparable Values where it is not blank.

     

    Comparable Values = 
IF (
    ISBLANK([This Year]) || ISBLANK([Last Year]),
    BLANK(),
    [This Year]
)

     

     

    Best Regards,
    Muhammad Yousaf

     

    If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

     

    LinkedIn

    • Jidnyasa2904's avatar
      Jidnyasa2904
      Icon for Helper I rankHelper I
      2 years ago

      This formula works if no other column is dependent on it. However, my case is a bit more complex. Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jidnyasa2904 

     

    Thanks for the reply from dharmendars007 and muhammad_786_1 .

     

    Jidnyasa2904 , could you please consider creating a calculated table? The following test is for your reference:

     

    My sample:

     

    Create a calculated table as follows:

     

    Filtered Table = 
FILTER(
    SUMMARIZE(
        'table',
        'table'[State],
        "ThisYearValue", [This year],
        "LastYearValue", [Last year]
    ),
    NOT(ISBLANK([ThisYearValue])) && NOT(ISBLANK([LastYearValue]))
)

     

     

    Output:

     

    If I am using a different data structure than yours, could you please provide sample data so that we can better help you? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.