Forum Discussion

rod_puente's avatar
rod_puente
Icon for Helper I rankHelper I
2 years ago
Solved

Help with DAX

Dears All   Pls, your help, I have a database in SQL called "table", and my problem is that I don't know how to have a measure that is not affected by the selection of 2 segments, which in my examp...
Help with DAX
  • Selva-Salimi's avatar
    Selva-Salimi
    2 years ago

    Hi rod_puente 

     

    The purpose of using two slicers is unclear to me, but you can follow these steps:

     

    1. create three tables for matrix and those two slicer ( you can write sth like summarize(your_table, year) to make it dynamic or just write 2023 and 2024 ) . keep in mind these three tables should not have any relationship with other tables.

    2. use one of these tables (table1) in slicer1, table2 in slicer2 and table3 in the matrix

    3. write a measure as follows:

    measure amount := if ( selectedvalue ( table3 [year]) =selectedvalue (table1 [year]) , calculate (sum(amount), filter (your_table , year= selectedvalue (table1 [year]) , if (selectedvalue ( table3 [year]) =selectedvalue (table2 [year]) , calculate (sum(amount), filter (your_table , year= selectedvalue (table2 [year]) )

     

    Your requirement is a bit unconventional, but I'm happy to assist. Could you provide more details on the rationale behind using two slicers? This approach seems complex, and I'm wondering if there's a specific reason you're choosing this method.

     

    If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago

Hi rod_puente 

use All() or ALLSELECTED() to ignore slicer selections

rod_puente's avatar
rod_puente
Icon for Helper I rankHelper I
2 years ago

Hi powerbiexpert22 

 

My problem is that the slicers should not cross, for example the "Year 1" slicer should affect only the "Amount: 2024" column, and the "Year 2" slicer should affect only the "Amount: 2023" column. When I apply the ALL() or ALLEXCEPT() functions, it does not work, and it seems that I need to make a combined function.