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...
  • 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
)
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
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

        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.