Forum Discussion

mhinia's avatar
mhinia
Frequent Visitor
2 years ago

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):

KEYVALUETIMEFLAG
50741YES
50112 
50133YES
5094 
5065 
51621 
511002 
51673 
51364 
51535YES
52521 
52572 
52693 
52604 
52365 

 

I'm making a matrix of them (KEY as row, TIME as column, VALUE as value):

However, I would need to filter the matrix so that only TIME that has FLAG=YES on ANY row is shown. If TIME has a FLAG=YES for any KEY, the value for all KEYs should be shown. Meaning that in the test data, only TIME 1, 3 and 5 should show. Like this: 

 

If I put in Power BI FLAG=YES as advanced filter in the visual, naturally KEY 52 is dropped completely and I don't get all values for 50 and 51:

So it is right to show only TIME 1, 3 and 5 but I need to see all the values for all the keys. 

 

The setup needs to be dynamic when I filter the keys. E.g. if I choose to look at only key 51, matrix shows only TIME 5 because it is the only timepoint that has FLAG=YES for KEY 51: 

 

I have tried to do a measure that should count the amount of FLAG=YES for each TIMEpoint and filter the matrix visual with the measure, but it is not working: 

FilterValue = CALCULATE(
    COUNT('Table'[FLAG]), 'Table'[FLAG]="YES", ALL('Table'[KEY]))

 

Thanks!

Need Help

2 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    2 years ago

    hello 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?

    • mhinia's avatar
      mhinia
      Frequent Visitor
      2 years ago

      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.