Calculation Groups with Disconnected Tables in Power BI/AAS
Below I will explain how to use Calculation Groups with Disconnected tables.
I know that some of this might be able to be done with other calculation groups. I find I have more flexibility when combining Calculation Groups with a disconnected table.
Below are some of my previous calculation group blog posts that might also be of interest:
My working example
In my working example below, I am going to show you how I create a disconnected table that uses the 3 measures shown below.
Creating the Disconnected table
As I have explained before Power BI – Using a Slicer to show different measures
Below is what my Disconnected table looks like which was called ‘Measure Table’
One of the things to note from above is that I added the “Measure Sort By” column to sort my disconnected measures in a particular order.
Creating the Disconnected measure
The next step is I had to create the disconnected measure which would get the inputs from the disconnected table above to get the right measure
Disconnected Measure = VAR MySelectedValue = SELECTEDVALUE ( 'Measure Table'[Measure Name], "Sales" ) VAR DynamicMeasure = SWITCH ( TRUE (), MySelectedValue = "Sales", [Sale], MySelectedValue = "Margin", [Gross Margin], MySelectedValue = "Unit Cost", [Units], BLANK () ) RETURN DynamicMeasure
Creating the Calculation Group Item
Finally, I get to the step where I show you how I link the disconnected table with the calculation group. This also allows me to set the Format String for each measure when using the calculation groups.
- I already had a Calculation Group created.
I right clicked and selected “New Calculation Item”
- I then gave it the name of Disconnected.
Under Expression I put in SELECTEDMEASURE()
- This allows me to get the measure that has been selected from my measure above [Disconnected Measure]
Then under Format String Expression, I put the following below which would format the measure based on the item
VAR MeasureName = SELECTEDVALUE ( 'Measure Table'[Measure Name], SELECTEDMEASURENAME () ) VAR CurrencyFormatDecimal = SWITCH ( TRUE (), MeasureName = "Margin", "#,##0.00 %", MeasureName = "Sales", "$#,##0", MeasureName = "Unit Cost", "#,##0.00", SELECTEDMEASUREFORMATSTRING() ) RETURN CurrencyFormatDecimal
- What I am doing above is for each particular measure I am formatting it based on the underlying measure
This is what it looks like once completed
- I then saved this to my PBIX file
Working version using the Disconnected Table with the Calculation Group
Below I show you what it looks like when using the Disconnected table with the Calculation Group
- As shown below is the report with no calculation group items selected
- As you can see above the order of the measure are following my sort order that I defined earlier.
- Now, when I click on the Calculation Group Item “Disconnected”, it then formats my measures as expected.
- I also have the added flexibility if required to select a single measure, and keep the formatting in place
In this blog post I have shown you how to create and configure a disconnected table and use it with a calculation group
I hope you found this useful and if you have any questions or comments please leave it in the comments section below!