One Visual Three Columns Dependent On Slicer

[Anonymous]

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)

[Anonymous]

@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.

[Anonymous]

@Anonymous 

thank you, but it doesn't work. When my Target slicer and Baseline slicer have the same date - it works. But it will never be the scenario. When I select different dates in Target and Baseline, then the table is blank.

Is there any other way?

[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

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

[Anonymous]

@Pragati11 

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.

@amitchandak 

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.

[Pragati11]

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