Help with DAX

[rod_puente]

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 example are years.

For this example I use the element called "Financial Reporting Matrix By Profitbase". I'm putting together a financial income statement that allows me to compare 2 selected years with a segmenter, as shown in the image:

The database is similar to this in SQL, but with milions of rows:

Which DAX function should I use or combine?

Regards

Rodrigo Puente

[Selva-Salimi · Accepted solution]

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]

Hi @rod_puente 

for Amount: 2024 , try to use All(year2) in your calculation , similarly , for amount 2023, try All(year1)

also it would be helpful if you can share power bi file along with sample data and what you are trying to acheive

[Anonymous]

Hi @rod_puente ,

I agree with powerbiexpert22 , if you have trouble writing a measure, please explain your needs and show the expected results in a picture.

Best Regards,
Wenbin Zhou

[rod_puente]

Hi @Anonymous 

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.

Regards

Rodrigo P

[powerbiexpert22]

Hi @rod_puente 

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

[rod_puente]

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.