topic Re: Format a power bi table visual by adding a condition to show value exceedance in DAX Commands and Tips

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

Basmall_B — Thu, 28 Jan 2021 22:22:52 GMT

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?

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

lbendlin — Sat, 30 Jan 2021 01:05:02 GMT

" 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.

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

Basmall_B — Sun, 31 Jan 2021 14:30:47 GMT

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.

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

Basmall_B — Wed, 03 Feb 2021 15:22:49 GMT

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