Compare value in dimension table

[eric0809]

Hi,

I have a pretty standard datamodel that consists of a fact table and several dimension tables, all many-to-one relationships, unidirectional towards the fact table.

In the dimension table (DIM) there is value X that represents a certain number. I would like to create a measure (instead of a calculated column) that returns true or false based on whether the value is bigger than e.g. 2. This measure will be used in a table that has the key column of the fact table (FACT), and some of the columns in DIM.

I might be overthinking, but I can't get it to work.

I thought I could just use a lookupvalue like so:

MEASURENAME = LOOKUPVALUE(DIM[ColumnToShow], DIM[KeyColumn], SELECTEDVALUE(FACT[KeyColumn]) > 2

, but the table just keeps loading and ends up failing.

What am I forgetting here?

[tamerj1]

Hi @eric0809 

try

MyMeasure =
CALCULATE (
    SELECTEDVALUE ( DIM[ColumnToShow] ),
    CROSSFILTER ( DIM[KeyColumn], FACT[KeyColumn], BOTH )
) > 2

[eric0809]

Hi, thanks!

The calculate-part returns the correct value, but once I add the '>2' part, the whole table blows up and shows all possible combinations from both the fact and dimension tables.

Any suggestions on that?

[tamerj1 · Accepted solution]

Hi Eric,

Please try

MyMeasure =
IF (
    CALCULATE (
        SELECTEDVALUE ( DIM[ColumnToShow] ),
        CROSSFILTER ( DIM[KeyColumn], FACT[KeyColumn], BOTH )
    ) > 2,
    1
)

[eric0809]

Hi,

This seems to work, thanks. The thing is, it only shows values for which the evaluation is true. I tried adding an alternative value (so IF( [...], 1, 0)), but that immediately made the table show every possible combination again. I am familiar with row context, but I can't figure out why this behaviour would be present, and how to deal with it.

I can't imagine calculated columns would be the only option here right..?

[tamerj1]

Hi Eric

If you have other measure in the table/matrix visual you should see balnks wehereever the condition is false. Just make sure measure in filter pane is set to ALL.

[tamerj1]

Hi Eric

If you have other measure in the table/matrix visual you should see balnks wehereever the condition is false. Just make sure measure in filter pane is set to ALL.