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.
Hello experts,
I am currently working on a viz as follwoing.
When I select the period as less than equal to I get this viz.
The measures are follwing
Budget YTD =
VAR _1 = MAX(DateTbl[Period Number])
VAR _2 = CALCULATE(CALCULATE([Budget], DateTbl[Period Number]<=_1, ALL(DateTbl[Period Number])), ALL(DateTbl[Period Name]))
RETURN _2
Actual YTD =
VAR _1 = MAX(DateTbl[Period Number])
VAR _2 = CALCULATE(CALCULATE([Actual], DateTbl[Period Number]<=_1, ALL(DateTbl[Period Number])), ALL(DateTbl[Period Name]))
RETURN _2
Budget = SUMX(Budget,Budget[Budget])
Actual = SUMX(Actuals,Actuals[Actual])
Is there any way to get the same viz as above when the user makes a single selection as follwoing.
There are other viz on the same table which might break if I let the user make a lesser than equal to slicer selection.
I can't figure out how can I feed in that condition in those two (Budget YTD and Actual YTD measure).
A sample data would be following
Period Name | ULLG | Budget | Date |
January | Opex | $5,535,316.16 | 1/1/2019 |
February | Opex | $5,595,571.34 | 2/1/2019 |
March | Opex | $5,662,290.42 | 3/1/2019 |
April | Opex | $6,542,861.36 | 4/1/2019 |
May | Opex | $5,160,639.86 | 5/1/2019 |
June | Opex | $4,521,289.08 | 6/1/2019 |
July | Opex | $4,705,957.44 | 7/1/2019 |
August | Opex | $4,119,022.64 | 8/1/2019 |
September | Opex | $4,167,515.15 | 9/1/2019 |
October | Opex | $4,826,702.24 | 10/1/2019 |
November | Opex | $6,086,002.73 | 11/1/2019 |
December | Opex | $5,500,882.34 | 12/1/2019 |
Budget Table
Period Name | ULLG | Actual | Date |
January | Opex | $5,656,756.53 | 1/1/2019 |
February | Opex | $5,456,041.26 | 2/1/2019 |
March | Opex | $6,421,766.07 | 3/1/2019 |
April | Opex | $6,154,725.54 | 4/1/2019 |
May | Opex | $4,697,044.68 | 5/1/2019 |
June | Opex | $5,053,559.26 | 6/1/2019 |
July | Opex | $4,854,606.80 | 7/1/2019 |
August | Opex | $4,403,921.72 | 8/1/2019 |
September | Opex | $4,748,459.55 | 9/1/2019 |
October | Opex | $5,314,571.03 | 10/1/2019 |
November | Opex | $205,960.43 | 11/1/2019 |
December | Opex | 12/1/2019 |
Actual Table
My date table is following
Any help is greatly appreciated
Solved! Go to Solution.
Hi @smpa01 ,
In my opinion, there’re relationship between DateTbl, Actual and Budget, so you might need to create a calculated table as slicer.
Please check following steps as below and see if the result achieve your expectation:
1. Create calculated table and use Period Number as slicer:
Table = DISTINCT(DateTbl[Period Number])
2. Create measure:
Measure 2 =
var sv = SELECTEDVALUE('Table'[Period Number])
return
IF(NOT(ISFILTERED('Table'[Period Number])),1,IF(MONTH(MAX(Actual[Date]))<=sv,1,BLANK()))
3. Add measure to filter:
4. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @smpa01 ,
In my opinion, there’re relationship between DateTbl, Actual and Budget, so you might need to create a calculated table as slicer.
Please check following steps as below and see if the result achieve your expectation:
1. Create calculated table and use Period Number as slicer:
Table = DISTINCT(DateTbl[Period Number])
2. Create measure:
Measure 2 =
var sv = SELECTEDVALUE('Table'[Period Number])
return
IF(NOT(ISFILTERED('Table'[Period Number])),1,IF(MONTH(MAX(Actual[Date]))<=sv,1,BLANK()))
3. Add measure to filter:
4. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-jayw-msftthanks for taking time out to look into this and providing a solution.
It made me realize that this requirement can only be achieved by using a disconnected table. I was hoping for a DAX magic to produce the result without having to do that but using the values in a slicer from the same table would filter out the rows that falls outside of "is equal" to range. So this requirement can't be met I gues the way I want it to happen.
Nevertheless thanks for showing me a workaround.
I also found a similar post in the community https://community.powerbi.com/t5/Desktop/Passing-Slicer-Selection-value-in-DAX-Filter/m-p/176312#M77...
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |