Forum Discussion
One Visual Three Columns Dependent On Slicer
Hi,
I have a table that has four fields. Customer (txt), Baseline (date), Target (date), Contract Value (decimal).
I want to create a matrix that will have the following fields:
- Customer
- Baseline Value (dynamic)
- Target Value (dynamic)
Baseline and Target will be selected from the slicers. I have these measures, but it doesnt seem right. I guess I need to apply somehow "All" or "AllExcept", but I am not sure how.
Scenario:
If I select Baseline to be 06/2020, I want to see Contract Value as of 06/2020. If I select Target to be 07/2020, I want to see Contract Value as of 07/2020.
Contract-Target =
VAR MyTarget = selectedvalue(fact_RMR[Target])
RETURN
calculate(sum(fact_RMR[Contract Value]),fact_RMR[Baseline]=MyTarget)
Contract-Baseline =
VAR MyBaseline = selectedvalue(fact_RMR[Baseline])
RETURN
calculate(sum(fact_RMR[Contract Value]),fact_RMR[Baseline]=MyBaseline)
5 Replies
- Pragati11
Super User
Hi Anonymous ,
Try modifying your DAX expressions as follows:
Contract-Target = VAR MyTarget = selectedvalue(fact_RMR[Target]) RETURN calculate(sum(fact_RMR[Contract Value]), FILTER(ALLSELECTED(fact_RMR), fact_RMR[Baseline]=MyTarget)) Contract-Baseline = VAR MyBaseline = selectedvalue(fact_RMR[Baseline]) RETURN calculate(sum(fact_RMR[Contract Value]), FILTER(ALLSELECTED(fact_RMR), fact_RMR[Baseline]=MyBaseline))Thanks,
Pragati
- amitchandak
Anonymous , Join both Baseline and Target dates with same date table. One join will be inactive. Activate that using userelation .
Now they will filter on the same month.
refer example: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
thank you for your response, but it doesn't work. My matrix with Customer, Baseline Value and Target Value is totally blank, no values, no rows.
Thank you for your response. However, I need these dates to be independent and will always be different (target WILL NOT equal baseline)... target can be June 2020, but baseline can be April 2020, so I am not sure if your solution will work in this scenario.
Anonymous
Try first create a new table using:
New Table = SUMMARIZE(fact_RMR,fact_RMR[Target],fact_RMR[Baseline])Then modify your formula filter with the selectedvalue in new table column, remember use the new table columns as the slicers.
Contract-Target = VAR MyTarget = selectedvalue(New Table[Target]) RETURN calculate(sum(fact_RMR[Contract Value]),fact_RMR[Baseline]=MyTarget) Contract-Baseline = VAR MyBaseline = selectedvalue(New Table [Baseline]) RETURN calculate(sum(fact_RMR[Contract Value]),fact_RMR[Baseline]=MyBaseline)Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
