Forum Discussion

Benjaminhz's avatar
Benjaminhz
New Member
6 years ago
Solved

Add IF condition on a measure

Hi all

 

I can't think of a solution for the following measure :

 

First, I compare for each row of a table (table 1) the difference of the value of a column with the value of another in a second table (table 2)

Here is the measure which works well :

Gap = AVERAGEX('Table1';'Table1'[Items in A]) - AVERAGE('Table2'[Items in B])
 
I'll get this :
 
Based on this measure, I can't think of another measure to count the number of line for which the result is > 0 and then <= 0
 
Thank you in advance.
 
 
Need Help
  • edhans's avatar
    edhans
    6 years ago

    Try these measures. Replace with your table/field names as needed:

     

    Gap Above 0 = 
CALCULATE(
    COUNTROWS('DataTable'),
    FILTER('DataTable','DataTable'[Gap] > 0)
)

Gap Less Than or Equal To Zero = 
CALCULATE(
    COUNTROWS('DataTable'),
    FILTER('DataTable','DataTable'[Gap] <=0)
)

     

    Drop those into their own card visuals. If you put them in the table you are showing, they will always return either 0 or 1 because of the table filter context.

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Try these measures. Replace with your table/field names as needed:

     

    Gap Above 0 = 
CALCULATE(
    COUNTROWS('DataTable'),
    FILTER('DataTable','DataTable'[Gap] > 0)
)

Gap Less Than or Equal To Zero = 
CALCULATE(
    COUNTROWS('DataTable'),
    FILTER('DataTable','DataTable'[Gap] <=0)
)

     

    Drop those into their own card visuals. If you put them in the table you are showing, they will always return either 0 or 1 because of the table filter context.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi , Benjaminhz

    We can use a measure as below to work on it.

    Gap2 Above 0 = 
CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Gap]>0))
    Gap2 below or equal 0 = 
CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Gap]<=0))

     

    Here’s a sample I made:

     

    URL:https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EY4WtB6FL-9Jh0rDb3uHIlgBdyOX8Xp4FFofxYufNrePeg?e=smimnN 

     

    If it doesn't meet your requirement, kindly share your sample data and expected result if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

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

     

    • Benjaminhz's avatar
      Benjaminhz
      New Member
      6 years ago

      Edhans, Eason,

       

      Thanks both for your prompt replies. It worked well. Thank you !