IF to select a column value

[praveen_k]

I am trying to use a IF statement to select a column. I have a measured column which calculates percentage change. When the percentage change (measure column) is greater than 0, ID column should be selected and viewed on report. eg- id total month measure 23 65 2 0 23 85 3 0.3 5 6 2 0 5 5 3 -0.16 2 10 2 0 2 11 3 0.1 When the measure column is -0.16, ID = 5 need to be displayed on the report.

[v-caliao-msft]

Hi Praveen_k,

According to your description, you need to display the ID which have measure value >0, right?

If that is the case, you can create a calculated column to display if the current have measure value >0 or not. I have tested it on my local environment, here is the sample DAX expression below for you reference.
Check = IF(IFfunction[measure]<0,1,0)
Sciler = IF(CALCULATE(SUM(IFfunction[Check]),ALLEXCEPT(IFfunction,IFfunction[id]))>0,"<0",">0")

Regards,

Charlie Liao

[praveen_k]

[Seth_C_Bauer]

@praveen_k I'm assuming you want to keep the measure column. And my other assumption is that you want to keep the measure if the value is greater than or equal to zero, and everything that is under should be the ID. Here is a calc column that will work for what you are asking. If this is not accurate, please clarify the discription.

Value = IF('table'[measure] >= 0, 'table'[measure], 'table'[id])

[Neuro81]

Hi @Seth_C_Bauer

Just a quick note on your formula regarding best practise

we should avoid putting the Table name next to the measure
ref: top 5 best practise tips

@praveen_kAs Eno suggested your calc column should work, but I'd suggest appending the result with something so that the reader knows that the value returned is either the Id or the result of a mesaure

[Seth_C_Bauer]

@Neuro81 Thanks for pointing that out. I just used the quick defaults on this one, but I typically have been referencing my calculated columns the same as normal columns (with table name), and measures as the only outlier (no table name). Must have mis-read this in my reading.

[praveen_k]

@Seth_C_Bauer @Neuro81 Thanks for your inputs. 

This is the actual measure calculation. 

I tried using your expression and this is what I get - 

On the reports, I would like to view the ID column when when measure2 > 0 and Null/Blank when measure2 < 0. 

Hope this clarifies my original question. 

[praveen_k]

Correction to the above reply - 

On the reports, I would like to view the ID column when when measure1 > 0 and Null/Blank when measure1 < 0. 

[Seth_C_Bauer]

@praveen_k This initial code is for a Calculated Column.

This should work for a measure: 

AsMeasure = IF([measure1] >= 0, VALUES('table'[id]), BLANK())

[praveen_k]

 

Error Message:

MdxScript(Model) (1, 51) Calculation error in measure 'Table'[Measure2]: A table of multiple values was supplied where a single value was expected.

@Seth_C_Bauer @Neuro81 

I tried to choose table and card visualizations on the report to display measure2 value, and I see this error.

Am I doign somehing wrong ?

[Seth_C_Bauer]

@praveen_k If you are going to filter it down to see the individual rows, you can use the original calculation as a calculated column. The slicer works with that.