topic Re: New Dax to calculate sum by changing join in DAX Commands and Tips

New Dax to calculate sum by changing join

jagadeesh3001 — Tue, 07 Jan 2025 18:04:36 GMT

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

Re: New Dax to calculate sum by changing join

lbendlin — Tue, 07 Jan 2025 21:33:41 GMT

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.

Re: New Dax to calculate sum by changing join

jagadeesh3001 — Tue, 07 Jan 2025 22:38:05 GMT

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

carry value adj

capacity

carry value adj

colletral swaps

netting

capacity

colletral swaps

n/a

Need test_dax as shown below

Product Product_Ref Amt Test_DAX

capacity

n/a

Blank

carry value adj

capacity

Blank

carry value adj

colletral swaps

Blank

netting

capacity

Blank

colletral swaps

n/a

Re: New Dax to calculate sum by changing join

jagadeesh3001 — Tue, 07 Jan 2025 22:48:18 GMT

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

Re: New Dax to calculate sum by changing join

SachinNandanwar — Thu, 09 Jan 2025 08:38:33 GMT

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

Re: New Dax to calculate sum by changing join

johnt75 — Wed, 08 Jan 2025 10:43:26 GMT

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

Re: New Dax to calculate sum by changing join

SachinNandanwar — Wed, 08 Jan 2025 15:09:40 GMT

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

Re: New Dax to calculate sum by changing join

johnt75 — Wed, 08 Jan 2025 12:02:19 GMT

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

Re: New Dax to calculate sum by changing join

SachinNandanwar — Wed, 08 Jan 2025 15:16:03 GMT

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.

Re: New Dax to calculate sum by changing join

Poojara_D12 — Wed, 08 Jan 2025 13:30:09 GMT

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

Re: New Dax to calculate sum by changing join

Anonymous — Thu, 09 Jan 2025 06:46:28 GMT

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.

Re: New Dax to calculate sum by changing join

SachinNandanwar — Thu, 09 Jan 2025 08:41:07 GMT

Thats great Anonymous.

Why TREATAS doesnt work in this case remains a mystery.

Re: New Dax to calculate sum by changing join

Anonymous — Thu, 09 Jan 2025 09:41:24 GMT

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.

Re: New Dax to calculate sum by changing join

SachinNandanwar — Thu, 09 Jan 2025 10:40:38 GMT

Thank you Anonymous for the detailed explaination.It now makes more sense. Appreciate it.

Re: New Dax to calculate sum by changing join

jagadeesh3001 — Thu, 09 Jan 2025 16:18:57 GMT

Thanks for your time & explanation.

Re: New Dax to calculate sum by changing join

jagadeesh3001 — Thu, 09 Jan 2025 17:21:34 GMT

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

carry value adj

capacity

Blank

carry value adj

colletral swaps

Blank

netting

capacity

colletral swaps

n/a

Re: New Dax to calculate sum by changing join

jagadeesh3001 — Mon, 13 Jan 2025 15:41:43 GMT

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])
)