How to build a DAX that can use a filtered dataset to add columns and rows to a table visual

[Quindici]

Hi,

I am probably overthinking this, but ...

The table on the left has my data. Some projects have "Money". For those items I need to make a list and "join" them to the items from the same data but where type = "GOODS"

The chart on the right shows what I would like to see.

I really did try a LOT of things before I ended up here...

Thanks, if anyone knows the answer and would care to assist I will be extremely appreciative.

[alena2k · Accepted solution]

Everything is possible, but sometimes it takes lots of clicking. Here is one way:

In Power Query
1. Duplicate query

2. Filter one for Type = MONEY

3. Filter another on for Type = GOODS and rename Type, Item, Amount to be Type1, Item1, Amount1

4. Append and sort by Project, fill down Type and Item, fill up Type1 and Item 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8vX3c40E0qHBLkDS0MBAKVYHJufu7+8SDKQdAwJ8XIPB0lhk/YMc/dwh0qZgaRdXN0yDTQ2Q5LAaHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Type = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Type", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "MONEY"))
in
    #"Filtered Rows"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8vX3c40E0qHBLkDS0MBAKVYHJufu7+8SDKQdAwJ8XIPB0lhk/YMc/dwh0qZgaRdXN0yDTQ2Q5LAaHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Type = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Type", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "GOODS")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Type", "Type1"}, {"Item", "Item1"}, {"Amount", "Amount1"}})
in
    #"Renamed Columns"

let
    Source = Table.Combine({Table6, Table7}),
    #"Sorted Rows" = Table.Sort(Source,{{"Project", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Type", "Item"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Type1", "Item1"})
in
    #"Filled Up"

Load to Power BI

5.  Create table as you shown where Amount, Amount 1 will be Sum

6.  Use Conditional formating by Amount to "supress" by Rule output of Project, Type, Item if Amount is blank

7.  Do the same with Project, Type1, Item1 if Amount1 is blank (I used white on grey to demonstrate the idea)

8.  Rename column headers to if needed

[v-juanli-msft · Accepted solution]

Hi @Quindici 

Thanks to alena2k's solution, i could use this succesfully.

Based on this, i make a test and show more details. 

Open Edit queries, paste the following code in advanced editor.

  "Source=****" is the original dataset, what i add and modify is from step #"filtered rows", you could see more details in my pbix.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8vX3c40E0qHBLkDS0MBAKVYHJufu7+8SDKQdAwJ8XIPB0lhk/YMc/dwh0qZgaRdXN0yDTQ2Q5DAMNgLKxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Type = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Type", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Type] = "MONEY")),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Type] = "GOODS")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Type", "Type1"}, {"Item", "Item1"}, {"Amount", "Amount1"}}),
    #"New Source" = Table.Combine({#"Filtered Rows1", #"Renamed Columns"}),
    #"Sorted Rows" = Table.Sort(#"New Source",{{"Project", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Type", "Item"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Type1", "Item1"})
in
    #"Filled Up"

Then close&&apply, when you add columns in the Table visual, click on "Amount" and "Amount" field and select "sum" as below.

Create a measure and set conditional formatting for "Project","Type" and "Item" columns as below

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alena2k · Accepted solution]

Everything is possible, but sometimes it takes lots of clicking. Here is one way:

In Power Query
1. Duplicate query

2. Filter one for Type = MONEY

3. Filter another on for Type = GOODS and rename Type, Item, Amount to be Type1, Item1, Amount1

4. Append and sort by Project, fill down Type and Item, fill up Type1 and Item 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8vX3c40E0qHBLkDS0MBAKVYHJufu7+8SDKQdAwJ8XIPB0lhk/YMc/dwh0qZgaRdXN0yDTQ2Q5LAaHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Type = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Type", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "MONEY"))
in
    #"Filtered Rows"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8vX3c40E0qHBLkDS0MBAKVYHJufu7+8SDKQdAwJ8XIPB0lhk/YMc/dwh0qZgaRdXN0yDTQ2Q5LAaHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Type = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Type", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "GOODS")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Type", "Type1"}, {"Item", "Item1"}, {"Amount", "Amount1"}})
in
    #"Renamed Columns"

let
    Source = Table.Combine({Table6, Table7}),
    #"Sorted Rows" = Table.Sort(Source,{{"Project", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Type", "Item"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Type1", "Item1"})
in
    #"Filled Up"

Load to Power BI

5.  Create table as you shown where Amount, Amount 1 will be Sum

6.  Use Conditional formating by Amount to "supress" by Rule output of Project, Type, Item if Amount is blank

7.  Do the same with Project, Type1, Item1 if Amount1 is blank (I used white on grey to demonstrate the idea)

8.  Rename column headers to if needed