Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Compare value in dimension table

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's avatar
    tamerj1
    4 years ago

    Hi Eric,

    Please try

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    try

    MyMeasure =
CALCULATE (
    SELECTEDVALUE ( DIM[ColumnToShow] ),
    CROSSFILTER ( DIM[KeyColumn], FACT[KeyColumn], BOTH )
) > 2
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi Eric,

        Please try

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