topic Re: Compare value in dimension table in DAX Commands and Tips

Compare value in dimension table

Anonymous — Mon, 09 May 2022 12:42:47 GMT

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?

Re: Compare value in dimension table

tamerj1 — Mon, 09 May 2022 14:25:42 GMT

Hi Anonymous

try

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

Re: Compare value in dimension table

Anonymous — Tue, 10 May 2022 08:39:04 GMT

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?

Re: Compare value in dimension table

tamerj1 — Tue, 10 May 2022 14:42:30 GMT

Hi Eric,

Please try

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

Re: Compare value in dimension table

Anonymous — Wed, 11 May 2022 07:22:05 GMT

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

Re: Compare value in dimension table

tamerj1 — Wed, 11 May 2022 08:22:20 GMT

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.

Re: Compare value in dimension table

tamerj1 — Wed, 11 May 2022 08:22:27 GMT

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.