topic Re: Help with DAX in DAX Commands and Tips

Help with DAX

rod_puente — Mon, 09 Sep 2024 20:54:36 GMT

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

Re: Help with DAX

powerbiexpert22 — Tue, 10 Sep 2024 01:15:08 GMT

Hi rod_puente

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

Re: Help with DAX

Anonymous — Tue, 10 Sep 2024 02:34:40 GMT

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

Re: Help with DAX

rod_puente — Tue, 10 Sep 2024 13:17:24 GMT

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

Re: Help with DAX

rod_puente — Tue, 10 Sep 2024 13:17:57 GMT

Hi powerbiexpert22

Re: Help with DAX

powerbiexpert22 — Tue, 10 Sep 2024 14:01:17 GMT

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

Re: Help with DAX

Selva-Salimi — Wed, 11 Sep 2024 02:18:44 GMT

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.