Help with DAX ALLSelected

[Vamshi_PBI]

Hi Team,

I need some help with writing dax formula. PFB details.

My data model is having 2 tables and the data modelling as below . My datasource is a combination of live and direct query.

DimProduct: 

Product	Product Key
Apple	1
Banana	2
Orange	3
Mango	4

FactSales: 

ProductForeignKey	Amount
1	10
1	10
2	20
3	30
4	40
4	40

So i would like to take product key, product foreign key into table visual and dispaly the total sum of amount (150) againist each row by using ALLSelected function as below.

All Amount = CALCULATE(SUM(FactSales[Amount]),ALLSELECTED(FactSales))

But as soon as I include ALL or ALLSelected a Cross Join is happening between productkey and productforeign key as below.

So, I would like to show total sum for all rows and Product Key filter should work, for example if i choose only Product 1 & 2, the total sum should be displaying for all rows is 30 (10+20).

Please help with this, any help is very much appreciated.

Thanks.

[Anonymous]

Hi @Vamshi_PBI

Your problem may be related to using the ALLSELECTED function in DirectQuery mode.

One possible solution is to use the KEEPFILTERS function instead of ALLSELECTED. The KEEPFILTERS function preserves the existing filters in the filter context and applies them to the specified column or table. For example, you can create a new measure like this:

All Amount = 
    CALCULATE(
        SUM(FactSales[Amount]),
        KEEPFILTERS(FactSales)
    )

If you still have problems, it's best to provide the pbix file with your expected results and be careful to remove sensitive data.

Regards,

Nono Chen

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

[Daniel29195]

@Vamshi_PBI 

All Amount = CALCULATE(SUM(FactSales[Amount]),ALLSELECTED(dimproduct[productname]))

If this answers your question  ,  mark it as the solution ✅ so can you can help  other people in the community find it easily .

[Vamshi_PBI]

Hi @Daniel29195 ,

With this approach , cross join wont happen, but total sum (for selected products or all products) is not reflected for all rows. 

Thanks.

[Daniel29195]

@Vamshi_PBI 

if you are intending of using all the product columns into the visual : 

All Amount = CALCULATE(SUM(FactSales[Amount]),ALLSELECTED(dimproduct))

[Vamshi_PBI]

Hi @Daniel29195 , Sorry for the late response.

This still resulting in cross join and total value is also not diplayed againist each row.

Thanks.

[Daniel29195]

@Vamshi_PBI 

please if possible, share the file so i can take a closer look at the issue . ( via google drive or dropbox)

best regards