Forum Discussion
Matrix column filtered by another column being not blank
Hello, I have data like below (only in reality it has much more values for all columns): KEY VALUE TIME FLAG 50 74 1 YES 50 11 2 50 13 3 YES 50 9 4 50 6 5 ...
eliasayyy
Memorable Member
Memorable Memberhello mhinia
create a new calculated table
AllKeyTimeCombinations = CROSSJOIN(VALUES('Table'[KEY]), VALUES('Table'[Time]))
add the key and time to from the new table to the column and row
create new measure
Filtered Value =
VAR CurrentKey = SELECTEDVALUE('AllKeyTimeCombinations'[KEY])
VAR CurrentTime = SELECTEDVALUE('AllKeyTimeCombinations'[Time])
RETURN
CALCULATE(SUM('Table'[Value]), 'Table'[KEY] = CurrentKey, 'Table'[Time] = CurrentTime, 'Table'[Flag] = "YES")
add the measure to values
on the build a visual panel , right click on Key and select show items with no data, do the same for time
result:
if you need all the values, then what's the point of flag?
Hi. Thank you! To clarify, the desired output is:
The point of FLAG is to filter out TIME 2 and 4 because those values have no FLAG=YES in any row.
For example, the FLAG=YES for KEY=50 in TIME=1 could mean that there was an error at the data point that is flagged, and I want to see what value KEY 50 and all the other keys get at that time.