Dax to show column values if related column contains blank in the pivot layout

[Noorudeen_MD]

Hi power bi users,

I am trying to create a Table that should show the list of values in the company column, where the corresponding column in table has blank/null then I should want to display the parallel record.

Above is the Master table I need to convert it to pivot like all the columns to row and if any column in master table has blank, I need to make a flag as “CHECK” or “OK”

And if the user select the particular “Attribute”/ “CHECK” on the pivot table it should display the

Company values corresponding to those blank values on the columns on the master table

Eg:

1. IF I click on the “uboreg” and “CHECK” in should display the list of company values In the Master table

Oneplus, Oppo, Samsung

1. IF I click on the “status” and “CHECK” in should display the list of company values In the Master table

Apple, oppo

Requirement

On the 'Checks tab', can you move this table to be left aligned and when you click a specific column status, can you have a table pop up that shows the list of entities missing that information. E.g., if I click on any items that say 'CHECK' I should see a table with a list of entities missing the information

Please find the PBIX FILE  download link

https://mega.nz/file/ShAGhaRI#nNh2eAxXP58PFwZB8VDnEPIkVQaTb8JflcD968cEzs0 

I hope anyone can help me out , Thanks in Advance I am attaching the sample pbix file.

[v-yalanwu-msft · Accepted solution]

Hi, @Noorudeen_MD ;

Here only need one table , first we could change the data in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUosKMhJBdKGRsZAUilWJ1rJCMjIy8/OTATSpmbmQDI/GywBUlGcmFtcmpcOUgsXNwExCwryIYJgIVOQUF5qQU5pMUJpLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, company = _t, uboreg = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"company", type text}, {"uboreg", type text}, {"status", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each true),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Attribute"}, {{"Count", each Table.RowCount(Table.SelectRows(_, each [Value]="")), Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Custom", each  let a=[Attribute] in if [Count] = 0 then 

   Table.SelectRows( #"Sorted Rows" ,each [Attribute]=a)
 else 
   Table.SelectRows( #"Sorted Rows" ,each [Attribute]=a and [Value]="")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Conditional Column", "Custom", {"ID", "Value"}, {"Custom.ID", "Custom.Value"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Custom.ID", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows1","",null,Replacer.ReplaceValue,{"Custom.Value"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom.Value"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Custom", each if [Count] = 0 then "OK" else "CHECK"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "checks"}})
in
    #"Renamed Columns"

The final show:

Then in desktop setting the tooltip.

The final show:

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