One table with 2 slicers changing 2 separate columns

[DaveW]

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.

[OwenAuger · Accepted solution]

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

Data 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]

Report page

[OwenAuger · Accepted solution]

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

Data 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]

Report page

[DaveW]

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?

Cheers:)

[OwenAuger · Accepted solution]

You're welcome 🙂
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