cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bbg_qw Frequent Visitor
Frequent Visitor

duplicated slicers and tables need variation between

I have two table visualization and 2 slicers that control them. They're mutually exclusive in that the first date slicer only controls the first table and second date slicer only controls the second. The tables are expected to be used as snapshots in time of the same data. The tables both pull from the same fact date, dollars, and total. The slicers both pull the date from the same date table. The result of the table when a date is selected is always only one row.

 

How can I use the resulting values to get a variance in a resulting table?

 

I have tried using values and selected values formulas to pull the date from the slicers then plugging them into lookup formulas to capture the corresponding dollar value but unfortunately when slicer date is pulled into lookupvalue formula using a variable it results to blank. But when I do the values/selected value formula alone and use a card visualization the date are pulled incorrectly but can't be used in the lookup.

 

slicer 1: 12/12/2018

slicer 2: 9/1/18

 

Table 1:

Date         Value        Qty

12/12/18     21             3

 

 

Table 2:

Date         Value        Qty

9/1/18          10            2

 

Variance:

 

Value          Qty

11                 1

5 REPLIES 5
ryan_mayu Established Member
Established Member

Re: duplicated slicers and tables need variation between

@bbg_qw

 

I will duplicate another table by using new table function.

 

Sheet4=Sheet3

 

Use each table's date column to control the data.

 

Measure = sum(Sheet3[value])-sum(Sheet4[value])
Measure = sum(Sheet3[value])-sum(Sheet4[value])
 
c1.JPG
bbg_qw Frequent Visitor
Frequent Visitor

Re: duplicated slicers and tables need variation between

It looks like you duplicated the table instead of referencing the same table and then doing the math.  Unfortunately I can not use a duplicated table because it's causing severe performance issues.

 

 

Community Support Team
Community Support Team

Re: duplicated slicers and tables need variation between

Hi @bbg_qw,

 

I'm afraid it is not available to achieve such an output if referring to the same date dimention table and the same actual data table.

 

Please duplicate the date dimention table, and keep each date dimention table to be unrelated to data table (in my test, it's Table3). You should drag date fields from two date dimention tables into two slciers.

 

Then, please create below measures:

slicer1 = SELECTEDVALUE('date dim'[Date])
filter1 = IF(SELECTEDVALUE(Table3[Date])=[slicer1],1,0)
Value measure = CALCULATE(SUM(Table3[Value]),FILTER(ALLSELECTED(Table3),Table3[Date]=[slicer1]))))

slicer2 = SELECTEDVALUE('date dim2'[Date])
filter2 = IF(SELECTEDVALUE(Table3[Date])=[slicer2],1,0)
Value measure2 = CALCULATE(SUM(Table3[Value]),FILTER(ALLSELECTED(Table3),Table3[Date]=[slicer2]))

variance = [Value measure2]-[Value measure]

Measures [filter1] and [filter2] should be added to "Visual level filters" of their corresponding table visuals, and set value to 1.

1.PNG

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bbg_qw Frequent Visitor
Frequent Visitor

Re: duplicated slicers and tables need variation between

We figured out a way to achieve this through use of UseRelationship Dax. 

Community Support Team
Community Support Team

Re: duplicated slicers and tables need variation between

Hi @bbg_qw,

 

Would you please kindly share the DAX formula where USERELATIONSHIP is used?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.