Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
Help is needed.
(for some reason all tables i post from excel are shown here from right to left , while they shoud be left to right - probably due to hebrew -english copy paste problems ... please note)
I want to show on a visual matrix the difference between number of registrants between all the dates the user selected in the slicer (meaning dynamic date filter) - if I have 3 dates: 1.1.2018 ; 1.5.2018 and 1.10.2018 I want to show the difference between 1: 1.5.2018 to 1.1.2018 and between 1.10.2018 to 1.5.2018.
I successfully managed to do this using the following DAX code: (when MyTable is the data table , Reg includes the number of registrants and date hold the date. please note that dates can be any date and not a day before or month before etc. ...)
% registrants = VAR previousDate = CALCULATE ( MAX ( MyTable[Date] ), FILTER ( ALLSELECTED ( MyTable), MyTable[Reg] > 0 && MyTable[Date]< MAX ( MyTable[Date] ) ) ) VAR previousREG = CALCULATE ( SUM ( MyTable[Reg] ), FILTER ( ALLSELECTED( MyTable) , MyTable[Date]= previousDate ) ) RETURN DIVIDE( SUM ( MyTable[Reg] ) -previousReg,previousReg,0)
on the following dataset :
Number of registrants | SCHOOL | FACULTY | SECTION | DATE |
10 | X | 1 | A | 1.1.2018 |
20 | Y | 1 | A | 1.1.2018 |
40 | Z | 2 | A | 1.1.2018 |
50 | T | 3 | B | 1.1.2018 |
32 | P | 4 | B | 1.1.2018 |
40 | U | 5 | B | 1.1.2018 |
10 | X | 1 | A | 5.1.2018 |
20 | Y | 1 | A | 5.1.2018 |
10 | Z | 2 | A | 5.1.2018 |
10 | T | 3 | B | 5.1.2018 |
32 | P | 4 | B | 5.1.2018 |
40 | U | 5 | B | 5.1.2018 |
Up until this point all goes well - superb! I get something like this (I put this table in excel to avoid starting to translate my entire dataset):
Number of registrants | date |
0% | 1.1.2018 |
-36.50% | 5.1.2018 |
However , I want to add to my matrix the ability to drill through / expend to the levels of the following Hierarchy:
this also work if I enable the user to filter the school , faculty , or section using a slicer
***PROBLEM starts here ***
it seems the drill through are not influence the ALLSELECTED dax statements. let say no filter were applied I get the following visual:
result i get | % change expected | SECTION | date |
0% | 0% | A | 1.1.2018 |
0% | 0% | B | |
-79% | -42.90% | A | 5.1.2018 |
-57% | -32.80% | B |
calculation made by power bi is actually (40-192)/192 instead of (40-70)/70 , because the VAR previousREG for some reason is not filtered by the drill through/expend I made.
so my question really is - can a drill through filter can applied and change this DAX code correctly ? I tried to change the code and remove the ALLSELECTED statement:
VAR previousREG = CALCULATE ( SUM ( MyTable[Reg] ), FILTER (( MyTable) , MyTable[Date]= previousDate ) )
However I received blanks instead of calculation results for some reason (why is that ?)
Thanks all for reviewing and trying to help
Elad
Solved! Go to Solution.
hi, @ellevy12
After my research, you could use ALLSELECTED Function instead of ALLSELECTED Function in your formula as below:
% registrants = VAR previousDate = CALCULATE ( MAX ( MyTable[Date] ), FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 && MyTable[Date]< MAX ( MyTable[Date] ) ) ) VAR previousREG = CALCULATE ( SUM ( MyTable[Number of registrants] ), FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate ) ) RETURN DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)
Result:
CALCULATE (
SUM ( MyTable[Number of registrants] ),
FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate )
)
you can also add other conditions in it.
Best Regards,
Lin
hi, @ellevy12
After my research, you could use ALLSELECTED Function instead of ALLSELECTED Function in your formula as below:
% registrants = VAR previousDate = CALCULATE ( MAX ( MyTable[Date] ), FILTER ( ALLSELECTED ( MyTable), MyTable[Number of registrants] > 0 && MyTable[Date]< MAX ( MyTable[Date] ) ) ) VAR previousREG = CALCULATE ( SUM ( MyTable[Number of registrants] ), FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate ) ) RETURN DIVIDE( SUM ( MyTable[Number of registrants] ) -previousReg,previousReg,0)
Result:
CALCULATE (
SUM ( MyTable[Number of registrants] ),
FILTER ( ALLEXCEPT(MyTable,MyTable[SECTION]) , MyTable[Date]= previousDate )
)
you can also add other conditions in it.
Best Regards,
Lin
Another question regarding to this data set -
how can i not display the min date ? since it's all 0 anyway due to not have diffrence of change ?
thanks
hi, @ellevy12
I have test a lot for it, I'm afraid it couldn't achieve for Matrix visual for now. The simplest method is filter it by slicer.
Sorry for the late reply for it.
Best Regards,
Lin
Amazing ! works ! i am struggling with this for a week (3 days of which i tried to understand how to login to the fourm using the new user system)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |