Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@Anonymous
I will duplicate another table by using new table function.
Sheet4=Sheet3
Use each table's date column to control the data.
Proud to be a Super User!
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.
Hi @Anonymous,
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.
Best regards,
Yuliana Gu
We figured out a way to achieve this through use of UseRelationship Dax.
Hi @Anonymous,
Would you please kindly share the DAX formula where USERELATIONSHIP is used?
Regards,
Yuliana Gu
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |