cancel
Showing results for
Did you mean:
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
Super Contributor

## Re: One table with 2 slicers changing 2 separate columns

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

Proud to be a Datanaut!

Super Contributor

## Re: One table with 2 slicers changing 2 separate columns

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

Proud to be a Datanaut!

3 REPLIES 3
Highlighted
Super Contributor

## Re: One table with 2 slicers changing 2 separate columns

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

Proud to be a Datanaut!

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?

Cheers

Super Contributor

## Re: One table with 2 slicers changing 2 separate columns

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

Proud to be a Datanaut!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 6 members 3,385 guests
Recent signins: