Dax help, ignore the pivot table filter then only get the year again

[lliu_western]

Hello Community, 

I am really stuck with a Dax right now and just wondering if I can get any help from the community.

So I have a pivit table in PowerBI where the rows are broken down to Years then months, and the colulms are in Category.

Example: 

2018       Type of Car       Year Made     

Jan

Feb

Mar

Apr

...

2019

Jan 

Feb

Mar

I need a value that ignore the piviot filter of the Years and Month so every month, it is showing the value that I need despite which month. I did that with a SELECTEDALL .

But then it gives me all the calender Years and Months starting from 2015. But I just want it to filter down into selected year again (so when my silicer selects 2019, it only shows 2019).

How can I use SELECTEDALL in the first place to get the desire value, then cut the Date range again?

Any helps and tips will be really appreciated, thank you. 

[lliu_western · Accepted solution]

Hi community, 

I found the solution to my own question. 

I used TOTALYTD(CALCULATE([the monthly measure I already create ]. ALLEXCEPT(Table,Table[Date - Uear], 'Filter' [All the columns that I want to keep the filter on])),DateTable[Date])

so instead of using ALLSELECTED, I used ALLEXCEPT to ignore the filters in the pivot table then use "TOTALYTD" to keep the date filter of this year again. 

[Greg_Deckler]

See if ALLEXCEPT or REMOVEFILTERS meets your needs better.

[amitchandak]

.The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible Can you share sample data and sample output

[lliu_western]

I have three table.

Table 1. is for the dates, which is represented by each row.

Table 2. is the categoty dimentions, i.e Type of Car , color of the car

Table 3. contains the value that I need to use for calculation.   

Example

2019           Category A      Category B   Category C ( [Category B * Category A ]/Sum(Category A)

Jan                 10                     0.5              5

Feb                 5                       0.9             4.5 

March            20                      0.7            14

April                5                      0.6             3

May               10                     0.8             8

I need a "total" of Catogey C, which is calculated as [Category C * Category A]/ sum(Category A)

I have figured out how to calculate the "total" of category C -  I had to used a sumx function

However, when I try to call this "total" value in Paginated report, it only shows me the first value in Category C. 

So I have been told, I need to create a measure that give me the "Total" of category C through out the pivot table, giving me the same value despite it's month. 

2019           Category A      Category B   Category C     Category D (Same value across - ignore the month filter)

Jan                 10                     0.5              5                      0.7  - I made up this number

Feb                 5                       0.9             4.5                    0.7 

March            20                      0.7            14                     0.7

April                5                      0.6             3                      0.7

May               10                     0.8             8                       0.7

I used ALLSELECTED in my Calculate(Expression, ALLSELECTED(TABLE[Date]) to achieve this goal. However, it now shows me all the Years/Months from 2015 (earliest date possible to latest date). I want the result table only display the year where user select in the date slicer (from the Date Table slicer). 

Sorry for the long question, it is a very very complex calculation. Thank you for your help. 

[v-gizhi-msft]

Hi,

Please try this:

SUMX(AllSELECTED(TABLE),expression)

Hope this helps.

Best Regards,

Giotto Zhi 

[lliu_western]

Please also see my question from here:

https://community.powerbi.com/t5/Desktop/Paginated-Report-HELP-Not-showing-the-same-as-PowerBI/m-p/9...

I am basically trying to solve the same problem as the question mentioned in the above link. I have been told that if I can create a measure that ignores the Months and just always give me the total value, then it might work. However, it then gives me Date Values starting from 2015 of the same value. But I only need the Date to start in the selected date in the Parameter. 

ANY HELP IS APPRECIATED! THANK YOU!