Forum Discussion
calculated row for matrix rows
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
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
10 Replies
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]
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.
- You could build a lookup table of data types and critical as Yes/No and the. Filter visual to only show Critical = Yes
Hi,
In the Query Editor, right click on the Item column and select "Unpivot other columns".
Mr. Mathur, Not sure what to expect but recieved error that several fields were in conflict with the unpivot other columns command.
Never heard of that error. It just always works for me. Retry
