topic Re: Comparison works weird in DAX Commands and Tips

Comparison works weird

Anonymous — Wed, 26 Feb 2020 10:39:07 GMT

The model contains of two tables/entities (note the terms are terms from the model, not from power BI itself):
- indicator
- data source

"indicator" is linked to "data source" as one-to-many: multiple data sources might be needed for a certain indicator. Each "data source" has a status - wherever it has or has not data model designed.

"data source" has following measures defined:
- total sources (=DISTINCTCOUNT(sources[ID]))

- format-ready sources (=CALCULATE(DISTINCTCOUNT(sources[ID]); sources[status] IN {"ModelReady"})
- % of format-ready sources (=sources[format-ready sources]/sources[total sources])

Now we need to tell wherever an "indicator" is format-ready or not. Obviously, it is ready when all of its data sources are ready, in other words, when '% of format-ready sources' measure equals to 100%.

So we use the formula within "indicator" table: IsDataReady = (sources[% of format-ready sources] = 1)

The problem:
- if we define a CALCULATED COLUMN with this formula, it does not work (returns incorrect false values)
- if we defined a MEASURE with exactly the same formula, it works as expected

Why we see this as a problem: from the business prospective, the attribute defining wherever a certain indicator is data-ready or not is not a measure over the range of indicators, it is and should be exactly the calculated attribute of an indicator; any aggregations can not and should not be applied to it. It's not a measure. It must be a calculated column - but it does not work being a calculated column.

Any ideas?

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 10:51:37 GMT

// Don't use floating-point arithmetic if you don't have to. It's inaccurate by nature so // equality should never be performed on such numbers. // And here's your column: [IsDataReady] = // calculated column in indicator var __allSources = RELATEDTABLE( 'sources' ) var __allSourcesCount = COUNTROWS( __allSources ) var __readySources = filter( __allSources, sources[status] = "ModelReady" ) var __readySourcesCount = COUNTROWS( __readySources ) RETURN __readySourcesCount = __allSources

Best

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:12:57 GMT

After replacing a typo in the last row __readySourcesCount = __allSourcesCount and using actual table/column names, the result is again incorrect.

Also the issue is not related to decimal vs. whole number comparison as it evaluates to false (for calculated column) even for rows where e.g. total sources = 1, format ready sources = 1 and there is a 1/1 division before comparing to 1

See the screenshot

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:27:47 GMT

First, the formula is not a measure. It's a calculated column formula. Second, I don't know your model's structure and data. Probably you have to make sure that you count distinct 'something' in source.

It looks like you're trying to use it in a visual as a measure... I'm confused.

Best
D

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:28:50 GMT

It's also possible that you are not following best practices... and hence the problems.

Best
D

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:31:05 GMT

Why don't you do

[IsFormatReady] = ([Total Sources] = [format-ready sources] ?

This should give you exactly what you need....

Best
D

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:45:07 GMT

Using this [IsFormatReady] = ([Total Sources] = [format-ready sources]) provides exactly the same results:
- it works correct if formula is used as a definition of a measure

- it doesn't work if the same formula is used as a definition of a calculated column

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:45:56 GMT

"you are not following best practices"

Not following what exactly?

Re: Comparison works weird

Anonymous — Wed, 26 Feb 2020 11:59:21 GMT

The principles of dimensional design. Too much to talk about.

I can assure you 100% that If you have a dimension with unique members and this dimension is connected 1:* to a fact table and you have 2 measures, [A] and [B], that work correctly for each and every member of the dimension, then a calc column of the form ([A] = [B]) must OUT OF NECESSITY return the correct result.

If it does not, then there must be something wrong with the model.

Best
D