Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User
Super User

@Anonymous

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

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.
Anonymous
Not applicable

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.