Forum Discussion

Vamshi_PBI's avatar
Vamshi_PBI
Frequent Visitor
2 years ago

Help with DAX ALLSelected

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: 

ProductProduct Key
Apple1
Banana2
Orange3
Mango4

 

FactSales: 

 

ProductForeignKeyAmount
110
110
220
330
440
440

 

 

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.

 

Need Help

6 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      Vamshi_PBI
      Frequent Visitor
      2 years ago

      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's avatar
        Daniel29195
        Community Champion
        2 years ago

        Vamshi_PBI 

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

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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.