Format a power bi table visual by adding a condition to show value exceedance

[Basmall_B]

Hi,

I have a power bi table visual which has columns from two tables. I have to work with data that I am pulling from a SQL server analysis cube so adding calculated columns is not an option for me. 

What  I am trying to achieve is to create a measure that I can bring into the table whether the value exceeds the set limit or not.

I would like to add a measure to the right of the visual to show if the "Value" column exceeds the "limit" column. if it does it should return a '1". The "Value " column is coming from Table B and the Limits is coming from Table A . There is a one to many relation built between these tables in the tabluar model. The measure condition should be to run the conditional check   against the "Limit"  value if available. If that column is empty nothing should be returned

Here is  my table visual 

I played with the following DAX formula but I can only show the value in a card when I click on a single  value . 

Exceedence =
VAR X =
CALCULATE(
SUM(Table A[Limits]),
FILTER(Table A,Table A[RD_NAMEKEY] = SELECTEDVALUE(Table A[RD_NAMEKEY]))
)
RETURN
IF(
SELECTEDVALUE(Fact_TableB[RD_VALUE]) > X,
1,0
)

Could some one help me where I am going wrong in my approach or is this even the right approach?

[Basmall_B · Accepted solution]

Hi All,

I got this working with some forum searching and getting a helping hand in reformatting my code and approach. Posting this for the benifit of others struggling with a similar problem.

My approach:

I created two measures

one to select the row value from fact table A which I called FactValue

FactValue = SELECTEDVALUE(Fact_A[RD_VALUE])

Two to compare the value with the lookup value from a Dimension Table of chemicals "B" where Limits column is the lookup value.

Here I used ISBlank to elminate the rows that dont have any look up value( only certain chemicals have these limits)

Exceedence =

VAR Y = [FactValue] // I am referencing my first measure here

VAR X =

    IF(ISBLANK(Y), BLANK (), SELECTEDVALUE (Table_B[LIMITS]) )// Checks if the lookup dimension has a limit value, if yes then return the result as 1 or 0//

VAR Result =

    IF (ISBLANK (X)||X = 0, BLANK (), IF ( Y > X, 1, 0 ) ) //Check if the given value is > than the Limit value//

RETURN

    RESULT

Here is my final result in a formatted table with out the use of calculated columns. This approach works best if you have relationships built in your model

Thank you

[lbendlin]

" I am pulling from a SQL server analysis cube so adding calculated columns is not an option for me"

That is no longer a restriction - change your connection from live to direct query.

[Basmall_B]

Hi 

Thank you for your response. I should have been more clear when i said "Adding a calculated column is not an option"In my scenario I dont maintain the cube and our DBA team is pretty strict on what we can do and can't do. They recomend to work with a live connection as the data gets updated almost weekly. And for visuals they insist that I only use a measure where possible. The code I attached works in the context of the visual; only when I click on a row and show the measure on a card. What I would like is to show the same measure as a column inside the table visual. When I add it as a measure column in the visual it just  sits there showing a spinning wheel on the top showing it is trying to do something. May be my syntax is not correct. Can you suggest if I need to change something in the syntax. I am not an expert in DAX . What our I do is through help from the forum and YouTube videos. Thank you.