calculated row for matrix rows

[dougjones]

I have a matrix defined from a table and want to check if all data is available for a particular row value. Can't seem to find an example of how to do this. 

E.G.  Objective is to derive the column AllData then filter the matrix on False Rows

item       Data1    Data2   Data3  ....       AllData

A              3            19         4                 True

B                            4          20                False

C              2                        2                  False

D             1             1          1                 True

E             1            32                             False 

Table

Item       Data   Value

A            Data1     3             

A            Data2     19

A            Data3      4

B            Data2      4

B            Data3      20

C            Data1      2

C            Data3      2

D            Data1      1

D            Data2      1

D            Data3      1

E             Data1     1

E             Data2     32

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the Item column and select "Unpivot other columns".

[dougjones]

Mr. Mathur, Not sure what to expect but recieved error that several fields were in conflict with the unpivot other columns command.

[Ashish_Mathur]

Never heard of that error.  It just always works for me.  Retry

[dougjones]

Mr. Mathur, 

The table is a mix of numeric, date and string values for the columns (about 60)

Full error as follows:

DataSource.Error: Microsoft SQL: The type of column "MANUFACTURER" conflicts with the type of other columns specified in the UNPIVOT list.
Details:
DataSourceKind=SQL
DataSourcePath=.\uswsqlexpress;usw
Message=The type of column "MANUFACTURER" conflicts with the type of other columns specified in the UNPIVOT list.
Number=8167
Class=16

This occured with several other columns which I removed from the query, but still end up with this error on a critical column to be included in the table.

[Seward12533]

Something like this shoudl work. 

Measure to check # of Data Types on a ROW of the Visual 
Num Data Types = COUNTROWS(VALUES(Table[Data])

Measure to calculate the total possible number of distinct Data Types 
Total Num Data Types =CALCULATE([Num Data Types]),ALL(Table))

ALLDATA = [Num Data Types]=[Total Data Types]

[dougjones]

Mr. Seward,

Sounds good, but there are several hundred "data types" and I am only interested in three that are mandatory and want to present the user with a report showing where critical data is missing from the table.  Thanks if you can guide me further in this quest.

[Seward12533]

You could build a lookup table of data types and critical as Yes/No and the. Filter visual to only show Critical = Yes

[dougjones]

Mr. Seward,

Beyond my pay grade but I will try tomorrow. 

[v-juanli-msft]

Hi @dougjones

Does the solution really helps you, if it works, could you kindly accept the solution as an answer, if not, please share the error with me?

Best Regards

Maggie

[dougjones · Accepted solution]

Maggie,

Ffinally solved the problem as follows

Created three calculated columns which set a flag of 1

   if the Data is Data1 and Value >0 1, 0

   if the Data is Data2 and Value >0 1, 0

   if the Data is Data3 and Value >0 1, 0

Then created a measure which when used in a matrix visualiation adds up the flags.

Then set a filter to show only iitems where the measure of the sum is < 3 

This produces the desired matrix I gave as an example in the original question and only shows items where there is a missing required data value or one or more values is 0.

This is easily modified for any number of required data elements for a given row by adding more calculated flag columns and using them in the sum measure calculation then change the filter to 1 less than the total number of required data elements for each row.

Thanks for all the suggestions