Need Help In Visualisation Table

[lgs1983]

Afternoon All,

Need some guidance on how I display the below information as the indicators show if something has been changed or made null(0 = No Change, 1 = Change, 2 = Made Null)

Item #      NameInd          AddressInd       

Item 1       1                       0

Item 2       0                       1

Item 3       2                       1

Item 4       1                       0

The question is how I can I display a summary of these with the aforementioned legend so people don't see just the numbers?

So for example it would look this.

                         Name      Address

No Change         1                 2

Updated             2                 2

Made Null          1                 0

The only way I could come up with is make my own table and create column calculations. This then loses the link between the dataset though.

If you need any further details please let me know.

Any help/guidance is appreciated.

[parry2k]

@lgs1983 sample solution attached, you can change it as per your need

[lgs1983]

@parry2k thanks for coming back to me on this.

Maybe I didn't word my initial issue properly, I do apologise if so.

So my dataset looks like the below:

Item #      NameInd          AddressInd       

Item 1       1                       0

Item 2       0                       1

Item 3       2                       1

Item 4       1                       0

So it doesn't have a row for each change type (NameInd, AddressInd) per Item. 

This was a customised table by myself to compare the difference between two tables in Power BI. Maybe I'm going the wrong route in formatting like this....

[parry2k]

@lgs1983 yes I used your dataset as you provided originally and transformed it in power query to achieve the result. Click advanced editor and check power query steps to understand how it is transformed to get the result.

You just need to plug your raw data in same format as you provided here and solution will work.

[lgs1983]

@parry2k 

Another fly in the ointment is that the indicators are calculated fields in regards to two tables and so can't do that in power query as far as I know as it's only based on the table you are in so therefore can't unpivot.

Though it's going to be over 300k rows I can import that in to Excel, save the file then import and unpivot. Nasty workaround if there's not a cleaner one.

[parry2k]

@lgs1983 not sure the logic behind calculated field, but may be there is opportunity to take that logic in power query and create calculated field in query instead of adding as a column using DAX.

[lgs1983]

@parry2k so all the indicators do is look at the same column in two tables and identify if there is a difference between them and places a 0,1 to highlight that.

It also looks at to see if one table is null and the other isn't and puts a 2 in but I'm thinking if I understand the above then I should be able to implement this myself.

[parry2k]

@lgs1983 ok, let me know if you need further assistance otherwise you have solution in place from your original post.

[lgs1983]

Is it possible then @parry2k  to do formulae in power query between various queries? Think I was using the wrong terminology before.

Apologies for the mislead.

[parry2k]

@lgs1983 share teh dax expression used to calculate indicators

[lgs1983]

@parry2k 

CanExportInd = 
IF(LOOKUPVALUE('DUNSForMonitoringERDData'[CanExport],'DUNSForMonitoringERDData'[DUNS], DUNSForMonitoring[DUNS]) = 
LOOKUPVALUE('PLC2PartyData'[CanExport],'PLC2PartyData'[RevenueCurrencyCode], DUNSForMonitoring[DUNS]),0,
IF(AND(LOOKUPVALUE('DUNSForMonitoringERDData'[CanExport],'DUNSForMonitoringERDData'[DUNS], DUNSForMonitoring[DUNS])<>"",
LOOKUPVALUE('PLC2PartyData'[CanExport],'PLC2PartyData'[DUNSNumber], DUNSForMonitoring[DUNS])=""),2, 1))