Measure works perfectly fine till I add a column from dimension table

[brickanalyst]

Hello everyone,

I'm working on variance analysis, so we have two different quotes. Let's say BlueQuote and YellowQuote
I'd like to share as many details as possible.

This is the part that I'm working on (it has another two more connections but I don't think it's effecting what I'm doing here)
As you can see dimension contains code and its english meaning, relationship columns are colored as red. (uid_code and attribute)

this is the measure I'm currently working on;

Appended Total Abs Variance Measure = 

VAR _total_attribute_abs_variance =
CALCULATE(
    SUMX (
        VALUES ( 'Appended Table'[Attribute] ),
        [Appended Abs Variance]
    )/*,
    CROSSFILTER(
        'Appended Table'[Attribute],
        dimension Table[UID_CODE],
        BOTH
    )*/,
    NOT ('Appended Table'[Attribute]) IN 
    {"some_columns_we_dont_need"}
)

RETURN 
    IF(
        HASONEVALUE('Appended Table'[Attribute]),
        [Appended Abs Variance] / _total_attribute_abs_variance,
        _total_attribute_abs_variance
    )

 And this measure is working perfectly fine when I compare with attribute (or uid_code) and problem becomes when I add english name of these attributes. I don't understand why it's happening but it looks like it's filtering for each english name. 
by the way uid_codes (attributes) are unique values and each one has unique english meanings.

My intention; How this attribute effect the variance total cost this 2.72% etc. Just to illustrate these two measure will be divided (below)

I think I'm so close to the result but I'm stuck here. I really want to understand why this cartesian look-alike happening?
and how can we tweak the dax measure so It works as expected.

Thanks!

[brickanalyst · Accepted solution]

@lbendlin 

Hey I worked around with chatgpt with sample table and it changed the last measure to this :

finally the last measure is giving what I want (for sample table)

I applied to my original project and it worked when I change ALLSELECTED to ALL
however I really want to understand the steps? Can you explain me what's going on here?


Lastly
I wanted to check the plugin but I can't remember...
when you write EVALUATE on the top and copy-paste your DAX code so it shows the tabular result or singular ?

[lbendlin]

EVALUATE must return a table.  Can be a single row/single column table.

[brickanalyst]

Hello @lbendlin 

That's what I thought even tho it doesn't make sense while we have the column in the dimension table
and I already tried that "creating related column in the fact table" and it still gives the same error.

I don't understand why but it measure returns the only where the measure it 100% 
I seperated portion / denominator and it returns same value that's my problem.


it works when I remove english name meaning column tho... I can't understand...

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[brickanalyst]

Column_UID	Assumption_UID	Attribute	Value	Table_Type
Project_1	abc-0001-abc	UID_1	$1,000	BlueQuote
Project_1	abc-0001-abc	UID_1	$1,200	YellowQuote
Project_1	abc-0001-abc	UID_2	$2,000	BlueQuote
Project_1	abc-0001-abc	UID_2	$2,100	YellowQuote
Project_2	abc-0002-abc	UID_1	$2,500	BlueQuote
Project_2	abc-0002-abc	UID_1	$3,000	YellowQuote
Project_2	abc-0002-abc	UID_2	$2,200	BlueQuote
Project_2	abc-0002-abc	UID_2	$2,200	YellowQuote
Project_3	abc-0003-abc	UID_1	$4,000	BlueQuote
Project_3	abc-0003-abc	UID_1	$4,500	YellowQuote
Project_3	abc-0003-abc	UID_2	$5,000	BlueQuote
Project_3	abc-0003-abc	UID_2	$2,500	YellowQuote

UID_CODE	SUMMARY_CODES	DETAIL_COST_SUMMARY
UID_1	1	Electricity
UID_2	2	Plumbing

There are two tables and I have created couple of measures 
(I WISH I COULD UPLOAD PBI SAMPLE FILE I COULDN'T MAKE IT)

Appended BlueQuote Calc = 
VAR _table = 
    ADDCOLUMNS(
        'AppendedTable',
        "IsNumeric", IF( ISERROR( VALUE('AppendedTable'[Value]) ), 0, VALUE('AppendedTable'[Value]) ),
        "Table Type", 'AppendedTable'[Table_Type]
    )
VAR _measure = SUMX( FILTER(_table, [Table Type] = "BlueQuote" ), [IsNumeric] )

RETURN _measure
----------------------------
Appended YellowQuote Calc = 
VAR _table = 
    ADDCOLUMNS(
        'AppendedTable',
        "IsNumeric", IF( ISERROR( VALUE('AppendedTable'[Value]) ), 0, VALUE('AppendedTable'[Value]) ),
        "Table Type", 'AppendedTable'[Table_Type]
    )
VAR _measure = SUMX( FILTER(_table, [Table Type] = "YellowQuote" ), [IsNumeric] )

RETURN _measure
----------------------------
Variance $ = [Appended BlueQuote Calc] - [Appended YellowQuote Calc] 
---------------------------
Abs Variance $ = ABS([Variance $]) 
---------------------------
Total $ Abs Variance = 
 
VAR _total_attribute_abs_variance =
CALCULATE(
    SUMX (
        VALUES ( 'AppendedTable'[Attribute] ),
        [Abs Variance $]
    ),
    NOT ('AppendedTable'[Attribute]) IN --some columns we can't use
    {"state_code", "project_name"}
)
RETURN _total_attribute_abs_variance
---------------------------------
Variable Effect % to Total Abs Variance = 

VAR _total_attribute_abs_variance =
CALCULATE(
    SUMX (
        VALUES ( 'AppendedTable'[Attribute] ),
        [Abs Variance $]
    ),
    NOT ('AppendedTable'[Attribute]) IN 
    {"state_code", "project_name"}
)

RETURN 
    IF(
        HASONEVALUE('AppendedTable'[Attribute]),
        [Abs Variance $] / _total_attribute_abs_variance,
        _total_attribute_abs_variance
    )

and this is how it looks on power bi sample file

@lbendlin I tried my best but it's the closes situation I have

[lbendlin]

Instead of a crossfilter consider creating a caclulated colun using RELATED in the fact table to pull in the dimension attribute.