Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
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]
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
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.
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]
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:)
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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |