New Dax to calculate sum by changing join

[jagadeesh3001]

Hi All,

I’m new to Dax, please help me get below achieved

fact (sample data)

product_id, product_ref, amt

1,n/a,10

2,n/a,20

3,capacity,30

3,colletral swaps,40
4,capacity,60

6,n/a,50

Dim product (sample data)

product_id, product

1,capacity

2,n/a

3,carry value adj

4,netting

5,tba sales

6,colletral swaps

Dim product ref (sampel data)

product_ref

N/a

capacity

colletral swaps

Using abv 3 tables

Pbi model

Fact (product_id) joined to dim product (product_id)

Fact (product_ref) joined to dim_product_ref (product_ref)

With these joins if I create a simple report with amt

Product, product_ref, amt

capacity,n/a,10

n/a,n/a,20

carry value adj,capacity,30

carry value adj,colletral swaps,40
netting,capacity,60

colletral swaps,n/a,50

Now I need a new Dax measure (test_dax) to get below out put

Product, product_ref, amt, test_dax

capacity,n/a,10,30

n/a,n/a,20,blank

carry value adj,capacity,30,blank

carry value adj,colletral swaps,40,blank
netting,capacity,60,blank

colletral swaps,n/a,50,40

As you see new Dax measure is calculated based off of summarized/sub set of (prod_ref, amt - by applying filter on prod = carry value adj) then left join  prod_ref with product to get amt

I have tried using treatas but didn’t get me desired output

Thanks in advance

[v-nuoc-msft · Accepted solution]

Hi @SachinNandanwar 

Please allow me to share some methods here.

I suggest you create a table with the correct relationship to "Dim product" like this:

Create a new table.

SummaryTable = 
VAR FilteredProductID = 
CALCULATETABLE(
    VALUES('Dim product'[Product ID]),
    'Dim product'[product] = "carry value adj"
)
VAR FilteredFactTable = 
CALCULATETABLE(
    'fact',
    'fact'[Product ID] IN FilteredProductID
    )
RETURN
SUMMARIZE(
        FilteredFactTable,
        'fact'[Product Ref],
        "SumAmt", SUM('fact'[Amount])
)

Additionally, to ensure that the visual displays as you intended, I also modified the relationship between "Dim product" and "fact" as follows:

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jagadeesh3001 · Accepted solution]

Here one more solution i found after multiple tries

this don't need any changes in data model/joins (also not needed to use treatas / userelationship)

test_dax = IF (SELECTEDVALUE('Dim Product'[Product]) = "carry value adj",
BLANK(),
CALCULATE( SUM('fact'[amt]),
'dim product ref'[product ref]=SELECTEDVALUE('dim product'[product]),
ALL('dim product'[product])
)

[v-nuoc-msft · Accepted solution]

Hi @SachinNandanwar 

Please allow me to share some methods here.

I suggest you create a table with the correct relationship to "Dim product" like this:

Create a new table.

SummaryTable = 
VAR FilteredProductID = 
CALCULATETABLE(
    VALUES('Dim product'[Product ID]),
    'Dim product'[product] = "carry value adj"
)
VAR FilteredFactTable = 
CALCULATETABLE(
    'fact',
    'fact'[Product ID] IN FilteredProductID
    )
RETURN
SUMMARIZE(
        FilteredFactTable,
        'fact'[Product Ref],
        "SumAmt", SUM('fact'[Amount])
)

Additionally, to ensure that the visual displays as you intended, I also modified the relationship between "Dim product" and "fact" as follows:

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jagadeesh3001]

Thanks for your time & explanation.

[SachinNandanwar]

Thats great @v-nuoc-msft.

Why TREATAS doesnt work in this case remains a mystery.

[v-nuoc-msft]

Hi @SachinNandanwar 

Let me try to explain the use of TREATAS to you.

First analyze your code:

VAR Filtered_carry_value_adj_Table =
    FILTER(
        'Dim product',
        'Dim product'[Product] = "carry value adj"
    )
VAR FilteredProductRefs =
    SELECTCOLUMNS(
        'Table_Carry_Value_Adj',
        "Product_Ref", 'Table_Carry_Value_Adj'[Product]
    )

The first step is to look for the value “Product” as “carry value adj” in the Dim table and create a table. Here you can run it alone and you will get the following result:

Note that the documentation describes the "If a value returned in the table expression does not exist in the column, it is ignored."

According to the table data you provided, there is no corresponding “carry value adj” data in the “Fact” table, so you will not get any results.

VAR RelatedFact =
    CALCULATETABLE(
        'fact',
        TREATAS(FilteredProductRefs, 'fact'[Product Ref])
    )

I have made the following changes to your code: Since “product id” is corresponding, consider using the id column.

Test_TREATAS = 
VAR FilteredProductRefs = 
    SELECTCOLUMNS(
        FILTER(
            'Dim Product',
            'Dim Product'[product] = "carry value adj"
        ),
        "Product id", 'Dim product'[Product ID]
    )
VAR RelatedFact = 
    CALCULATETABLE(
        'fact',
        TREATAS(FilteredProductRefs, 'fact'[Product ID])
    )
RETURN
    SUMX(RelatedFact, 'fact'[Amount])

However, please note that this way the data corresponds to the product id, which means that you can't use this function to get the expected result. Therefore, I recommend that you abandon the use of this function.

actual result

Expected results

I hope I've explained this clearly.

TREATAS function - DAX | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SachinNandanwar]

Thank you @v-nuoc-msft for the detailed explaination.It now makes more sense. Appreciate it.

[Poojara_D12]

Hi @jagadeesh3001 

To create the test_dax measure:

Condition: If the product is "carry value adj", return BLANK().

Otherwise, calculate the sum of amt by filtering product_ref and matching it with the product_ref in the fact table.

TREATAS: Applies the filter on product to get the correct amt based on the condition.

Use the following DAX formula for test_dax:

test_dax = 
IF(
    SELECTEDVALUE(DimProduct[product]) = "carry value adj",
    BLANK(),
    CALCULATE(
        SUM(Fact[amt]),
        FILTER(
            DimProductRef,
            DimProductRef[product_ref] = SELECTEDVALUE(Fact[product_ref])
        ),
        TREATAS(
            VALUES(DimProduct[product]),
            DimProduct[product]
        )
    )
)

This will give you the desired output, showing amt based on the filtered conditions.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[jagadeesh3001]

Thanks for your time & dax formula, but this is giving same amt as original amt which is not as expected.

Product Product_Ref Amt Test_DAX

capacity

n/a

10

10

n/a

n/a

20

20

carry value adj

capacity

30

Blank

carry value adj

colletral swaps

40

Blank

netting

capacity

60

60

colletral swaps

n/a

50

50

[lbendlin]

As you see new Dax measure is calculated based off of summarized/sub set of (prod_ref, amt - by applying filter on prod = carry value adj) then left join  prod_ref with product to get amt

I don't see that.  Please walk me through an example for product 1 and another example for product 4.

[jagadeesh3001]

here is current report based on joins explained abv

i need below "test_dax"

if i filter original data for Product = "carry value adj"
subset of product_ref, amt looks like below (which is what looked up to populate in new test_dax)

i would like to know how this can be done in DAX

[jagadeesh3001]

This is current report based on all joins explained (Fact to Product based on Product_iD; to Product_ref based on product_ref)

Product Product_Ref Amt

capacity

n/a

10

n/a

n/a

20

carry value adj

capacity

30

carry value adj

colletral swaps

40

netting

capacity

60

colletral swaps

n/a

50

Need test_dax as shown below

Product Product_Ref Amt Test_DAX

capacity

n/a

10

30

n/a

n/a

20

Blank

carry value adj

capacity

30

Blank

carry value adj

colletral swaps

40

Blank

netting

capacity

60

Blank

colletral swaps

n/a

50

40

[SachinNandanwar]

Yes TREATAS isnt working.Even I tried it and I am completly stumped as to why it isnt working.Either its a bug or I am doing something totally silly. I hope its not the latter.

The sample PBIX file is attached here.

There are 2 measures in the attached report that use TREATAS, one referencing the Filtered Table and other referencing the Table variable in the measure and both arent working.

Hope the DAX gurus in thes forum can help solve the mystery

[johnt75]

Both of your measures are attempting to return a table, but measures can only return scalar values, not tables.

[SachinNandanwar]

Yes.I somehow missed that.But the measure should had thrown an error and it didnt.

I changed the measure to return a scalar value and still no output.

Test_DAX_Filtered_table = VAR Filtered_carry_value_adj_Table =
    FILTER(
        'Dim product',
        'Dim product'[Product] = "carry value adj"
    )

VAR FilteredProductRefs =
    SELECTCOLUMNS(
        'Filtered_carry_value_adj_Table',
        "Product_Ref", 'Filtered_carry_value_adj_Table'[Product]
    )
   
VAR RelatedFact =
    CALCULATETABLE(
        'fact',
        TREATAS(FilteredProductRefs, 'fact'[Product Ref])
    )
RETURN
    SUMX(RelatedFact, 'fact'[Amount])

Edit: I have fixed the measures and uploaded a new file here

[johnt75]

"carry value adj" doesn't exist in the 'fact'[product ref] column, so there is nothing to return.

[SachinNandanwar]

My understanding of the flow is ,

Filter Dim Product table on "carry value adj" and return product id which is 3 and then use this id to return Product ref column values "capacity" and "collateral swaps" from fact table. 

In the next step, get a match for "capacity" and "collateral swaps" across the tables Dim product and Fact(this is where TREATAS comes into picture) and then display the Amount across each Product from the Dim Product table.

Since we only have "capacity" and "collateral swaps", take the matching amount 30 and 40 respectively and display it against the "capacity" and "collateral swaps" values of the Product columns in the Dim Product table.

the final output should be something like this

In this flow the Product_Ref lookup table is completly ignored.It still doesnt explain why TREATAS isnt working.

I deleted the table Product Ref from model and it made no difference and then readded the table Product Ref and added the value "carry value adj" to it and still no output.