cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DaveW Regular Visitor
Regular Visitor

One table with 2 slicers changing 2 separate columns

I'm using PBI Report Server and using Direct Query Mode. 

I have a source table with rows of CostCentre Codes with a Version and a Total $ value

CCCode	Version	Total
123/123	1	120
123/123	2	240
123/123	3	360
123/123	4	480

I want to do a comparison between Versions of the CCCode with 2 independent Slicers. So 1st Slicer would select Version A and 2nd Slicer would select Version B.  The selection of the Version would return its relative Total value.

I would also like a calculation of the variance between Total A and Total B

 

CCCode	  Version A	Total A	   Version B	Total B	Variance (B-A)
123/123	  1	        120	   3            360     240	

I've built a report tha has 2 tables with a slicer for each but would like to merge the 2 tables into 1 table

I understand there are some limitations with measures runnig Direct Query mode.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: One table with 2 slicers changing 2 separate columns

@DaveW 

 

I would recommend setting up your data model in a similar way to this article. In the linked article, the dimension in question was Date, but the same logic applies. You have one fact table but two copies of the dimension table in question.

 

I've uploaded a sample PBIX here.

 

First set up your tables/relationships like this, i.e. with additional Version A & Version B tables (should be possible from your DirectQuery source). Relationship between Version A & Version B should be inactive and 1:1.

 

 

image.pngData model

 

 

Then create measures like this:

Total A = 
SUM ( Data[Total] )

Total B = 
CALCULATE ( 
    [Total A],
    ALL ( 'Version A' ),
    USERELATIONSHIP ( 'Version A'[Version A], 'Version B'[Version B] )
)

Variance (B-A) = 
[Total B] - [Total A]

 

 

image.pngReport page

 



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

Proud to be a Datanaut!




Super User
Super User

Re: One table with 2 slicers changing 2 separate columns

You're welcome Smiley Happy
The reason for the inactive relationship is that we want only want one of the VersionA/VersionB filters applying at any time.

 

The model is set up so that the VersionA filter applies by default since it has an active relationship with the fact table. So in the Total A measure we get the sum of the Total column with the VersionA filter applied.

 

The VersionB filter is normally inactive, but is only activated within the Total B measure (as well as clearing the VersionA filter).

 

Cheers

Owen



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

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: One table with 2 slicers changing 2 separate columns

@DaveW 

 

I would recommend setting up your data model in a similar way to this article. In the linked article, the dimension in question was Date, but the same logic applies. You have one fact table but two copies of the dimension table in question.

 

I've uploaded a sample PBIX here.

 

First set up your tables/relationships like this, i.e. with additional Version A & Version B tables (should be possible from your DirectQuery source). Relationship between Version A & Version B should be inactive and 1:1.

 

 

image.pngData model

 

 

Then create measures like this:

Total A = 
SUM ( Data[Total] )

Total B = 
CALCULATE ( 
    [Total A],
    ALL ( 'Version A' ),
    USERELATIONSHIP ( 'Version A'[Version A], 'Version B'[Version B] )
)

Variance (B-A) = 
[Total B] - [Total A]

 

 

image.pngReport page

 



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

Proud to be a Datanaut!




DaveW Regular Visitor
Regular Visitor

Re: One table with 2 slicers changing 2 separate columns

This has worked. Thanks for that.  I was going to try to import 2 versions of the same table but it still wouldn't have been as agile as this solution

Can you confirm why the relationship between tables VersionA and VersionB is not active?

 

CheersSmiley Happy

Super User
Super User

Re: One table with 2 slicers changing 2 separate columns

You're welcome Smiley Happy
The reason for the inactive relationship is that we want only want one of the VersionA/VersionB filters applying at any time.

 

The model is set up so that the VersionA filter applies by default since it has an active relationship with the fact table. So in the Total A measure we get the sum of the Total column with the VersionA filter applied.

 

The VersionB filter is normally inactive, but is only activated within the Total B measure (as well as clearing the VersionA filter).

 

Cheers

Owen



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

Proud to be a Datanaut!