Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everybody.
There is a list of branches, different indicators are calculated for them. Is it possible to make certain indicators are calculated for certain branches? When a branch was selected by the slicer, were the lines with measures for that branch displayed? I tried to KEEPFILTERS but it leaves the line with the indicator empty, but it needs to disappear.
For example, part of a big table
Branch | Sum |
Branch1, sales | 100 |
Branch1, sales | 150 |
Branch2, sales | 200 |
Branch2, sales | 250 |
Branch3, sales | 110 |
Branch1, expense | 50 |
Branch1, expense | 60 |
Branch2, expense | 80 |
Branch3, expense | 35 |
Result - sales measure for 3 should not be considered, only expense
Measures | Sum |
Branch1 | |
Sales | 250 |
Expense | 110 |
Branch2 | |
Sales | 450 |
Expense | 80 |
Branch3 | |
Expense | 35 |
Solved! Go to Solution.
First you should split the Branch column into 2: one for Branch and the other for sales/expenses. You can Use this code in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEvOMNRRKE7MSS1WUFBQ0lEyNDBQitXBIWeKLGeEImdkgEcORZ8xqpmGaPalVhSk5hWnQmRN8UmaodkIlQTKWKDZh6LN2FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Sum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Sum", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Branch", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Branch.1", "Branch.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Branch.1", type text}, {"Branch.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Branch.1", "Branch"}, {"Branch.2", "Type"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Type", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Type", Text.Clean, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each true)
in
#"Filtered Rows"
To get this structure:
Ideally you should then create Dimension Tables for Branch and Type (recommended). This is how the model looks:
Then, with these measures:
Sum Amount = SUM(FactTable[Sum])
Exc br3 sales =
SUMX (
FactTable,
CALCULATE (
IF (
MAX ( 'Dim Branch'[dBranch] ) = "Branch3"
&& MAX ( 'Dim Type'[dType] ) = "sales",
BLANK (),
[Sum Amount]
)
)
)
You will get the following:
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
First you should split the Branch column into 2: one for Branch and the other for sales/expenses. You can Use this code in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEvOMNRRKE7MSS1WUFBQ0lEyNDBQitXBIWeKLGeEImdkgEcORZ8xqpmGaPalVhSk5hWnQmRN8UmaodkIlQTKWKDZh6LN2FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Sum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Sum", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Branch", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Branch.1", "Branch.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Branch.1", type text}, {"Branch.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Branch.1", "Branch"}, {"Branch.2", "Type"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Type", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Type", Text.Clean, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each true)
in
#"Filtered Rows"
To get this structure:
Ideally you should then create Dimension Tables for Branch and Type (recommended). This is how the model looks:
Then, with these measures:
Sum Amount = SUM(FactTable[Sum])
Exc br3 sales =
SUMX (
FactTable,
CALCULATE (
IF (
MAX ( 'Dim Branch'[dBranch] ) = "Branch3"
&& MAX ( 'Dim Type'[dType] ) = "sales",
BLANK (),
[Sum Amount]
)
)
)
You will get the following:
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
Please provide sample data or a PBIX file, and a depiction of the expected outcome
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |