cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
OwenAuger Super Contributor
Super Contributor

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!




View solution in original post

OwenAuger Super Contributor
Super Contributor

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!




View solution in original post

3 REPLIES 3
Highlighted
OwenAuger Super Contributor
Super Contributor

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!




View solution in original post

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

OwenAuger Super Contributor
Super Contributor

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!




View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 3,385 guests
Please welcome our newest community members: