How to filter a calculated measure with a measure and how to filter a pie chart with a measure

[yaniso92]

Hi everyone,
I’ve got a problem, I have the tab below in Excel

I have two objectives at the end:

A) Create a measure to get for each item the matching combination between the system selected and all the sub-syst

As an example for the System 1 my objective is to get the tab below

B) Create a Pie chart for each system as an example for the system 1 below

What I’ve done so far to solve my problems:

1. I’ve unpivoted the Excel Tab to get the table1 below

2. I’ve created a look up table for system and sub system in Excel the table2

3. I’ve linked both tab  name column with a relationship

4. In Power Bi I’ve created a matrix where I’ve filtered System1 and all the sub System

5.I’ve created a measure to get only the value of sub-syst selected that match the value of System1

Measure = IF(AND(CALCULATE(SUM(Table1[Value]),Table2[Category]="Sub-Syst")=1,CALCULATE(SUM(Table1[Value]),Table2[Category]="System")=1),"MATCH")

6.Then I’ve tried to create a measure to get these data with a calculate measure but it didn't work

Measure 2 = calculate(SUM(Table1[Value]),filter(Table1,CALCULATE(SUM(Table1[Value]),Table2[Category]="Sub-Syst")=1),FILTER(Table1,CALCULATE(SUM(Table1[Value]),Table2[Category]="System")=1))

7. Since it didn’t work I’ve just filter the tab I’ve created  in step 2) with the measure in step 5) and It was working but to be honest I have no idea why this filter is working and why my measure 2 doesn’t return the same result.

I’ve solved my first problem and I’ve get the tab that I wanted but the Problem is that first I’m oblige to use the page filter and secondly every time I try to put these data in a pie chart and use the measure filter my data are disappearing.

TO be honest I have no idea on how to get the chart PIE

Thanks in advance for your help.

[yaniso92]

@Anonymous  First thanks for your help.

I've attached the power BI file to this LINK
Your solution is working only for System 1 but if I try to filter the system 2 it's not working.
I've created a measure to  get for each item the matching combination between the system selected and all the sub-syst.

To do that I've created the measure that I've  called MEASURE( Measure = IF(AND(CALCULATE(SUM(Table1[Value]),Table2[Category]="Sub-Syst")=1,CALCULATE(SUM(Table1[Value]),Table2[Category]="System")=1),"MATCH") )  in my tab and I've filtered my tab with this measure.

The thing is that when I filter my tab, the pie chart is not filtered in the same way, see the example below and When I try to filter the pie chart with the measure I've created the Pie chart disappear.

Thanks again for your help.

Best regards.

[Anonymous · Accepted solution]

Hi @yaniso92 ,

According to your description, here are my steps you can follow as a solution.

(1) We create tables. Please refer my pbix file.

Click "transform data" to enter the power query editor, open the "Advanced Editor" and copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRSgyuLIaxDZVidZCkoDJGWGRKk3SLgbLouoxwG2iE00AjAgbC5dD0GeO2zBjVMgM0GZiBxqi6THAbaILh+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Name", type text}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] = "aSystem 1" or [Name] = "System 2")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Item"}, #"Table (3)", {"Item"}, "Table (3)", JoinKind.LeftOuter),
    #"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"Name", "Value"}, {"Name.1", "Value.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table (3)", each ([Value] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name", "Cate"}, {"Name.1", "Sub"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRSgyuLIaxDZVidZCkoDJGWGRKk3SLgbLouoxwG2iE00AjAgbC5dD0GeO2zBjVMgM0GZiBxqi6THAbaILh+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Name", type text}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] <> "aSystem 1" and [Name] <> "System 2"))
in
    #"Filtered Rows"

Cate = SUMMARIZE(FILTER(Table2,[Category]="System"),[Name])

Sub = SUMMARIZE(FILTER('Table2',CONTAINSSTRING([Category],"Sub")),[Name])

(2) We can create a measure. 

Measure 4 = CALCULATE(SUM('Table (2)'[Value.1]),FILTER('Table (2)',[Cate] in VALUES(Cate[Name])&&[Sub] in VALUES(Sub[Name])))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi @yaniso92 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Measure 3 = CALCULATE(SUM('Table1'[Value]),FILTER(ALLSELECTED('Table1'),'Table1'[Name]<> "System 1" && 'Table1'[Name]=MAX('Table1'[Name])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[yaniso92]

@Anonymous  Thank you so much. It's working!