Forum Discussion

Zoko's avatar
Zoko
Frequent Visitor
8 years ago
Solved

Matrix showing only rows having values in every columns

Hey everyone !

I was wondering if there are any ways to make my matrix shows only rows having values in all my columns.
In my case i have companies names as rows and 2 columns with 2 different roles.
One company can have either one or both roles.
I want my matrix to show only companies with 2 roles.

Thanks for helping !

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Zoko,

     

    The correct check value for yellow company should not be marked as 0, because it has no value for "Cedante" role, right? 

     

    In my above post, such a scenario returns expected check value. Not sure how your source dataset is, maybe you should post some dummy data here. By the way, are "Somme de REFID" and "Somme de BUID" original fields in dataset, or calculated measures or aggregated columns?

     

    Besides, please try this measure to check if it works in your scenario.

    check2 =
CALCULATE (
    IF ( SUM ( Test2[REF ID] ) = BLANK (), 1, 0 ),
    FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Ced" )
)
    + CALCULATE (
        IF ( SUM ( Test2[BUID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Ced" )
    )
    + CALCULATE (
        IF ( SUM ( Test2[REF ID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Rea" )
    )
    + CALCULATE (
        IF ( SUM ( Test2[BUID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Rea" )
    )

    Regards,

    Yuliana Gu

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Zoko,

     

    Please add below measure to "Visual Level filter" and set its value to 0.

    check =
 (
    CALCULATE (
        COUNTROWS ( Test2 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[REF ID] = BLANK () )
    )
        + 0
)
    + (
        CALCULATE (
            COUNTROWS ( Test2 ),
            FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[BUID] = BLANK () )
        )
            + 0
    )

     

    Best regards,

    Yuliana Gu

    • Zoko's avatar
      Zoko
      Frequent Visitor
      8 years ago

      Hi v-yulgu-msft

      Thanks a lot for your help !
      It's almost working :

      As you can see the yellow company has a check value of 0 meanwhile it has both value in only one role.
      A company will always have a REFID if it exists.
      My measure is the following :

      check = 
 (
    CALCULATE (
        COUNTROWS ( REF_COMPANY_ROLE_VW );
        FILTER ( ALLEXCEPT ( REF_COMPANY_ROLE_VW; REF_COMPANY_ROLE_VW[COMPANY_SHORT_NAME] ); REF_COMPANY_ROLE_VW[REFID] = BLANK () )
    )
        + 0
)   + (
        CALCULATE (
            COUNTROWS ( REF_COMPANY_ROLE_VW );
            FILTER ( ALLEXCEPT ( REF_COMPANY_ROLE_VW; REF_COMPANY_ROLE_VW[COMPANY_SHORT_NAME] ); REF_COMPANY_ROLE_VW[BUID] = BLANK () )
        )
            + 0
    )


      Thanks for your time.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Zoko,

         

        The correct check value for yellow company should not be marked as 0, because it has no value for "Cedante" role, right? 

         

        In my above post, such a scenario returns expected check value. Not sure how your source dataset is, maybe you should post some dummy data here. By the way, are "Somme de REFID" and "Somme de BUID" original fields in dataset, or calculated measures or aggregated columns?

         

        Besides, please try this measure to check if it works in your scenario.

        check2 =
CALCULATE (
    IF ( SUM ( Test2[REF ID] ) = BLANK (), 1, 0 ),
    FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Ced" )
)
    + CALCULATE (
        IF ( SUM ( Test2[BUID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Ced" )
    )
    + CALCULATE (
        IF ( SUM ( Test2[REF ID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Rea" )
    )
    + CALCULATE (
        IF ( SUM ( Test2[BUID] ) = BLANK (), 1, 0 ),
        FILTER ( ALLEXCEPT ( Test2, Test2[Company] ), Test2[Role] = "Rea" )
    )

        Regards,

        Yuliana Gu

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Could just use an is not blank visual level filter on your two columns

    • Zoko's avatar
      Zoko
      Frequent Visitor
      8 years ago

      Yeah, I tried that but in fact, i have 2 values per column, thus 4 values in total.
      I want this 4 values to be shown, so first i assigned for both of my values labels a not blank filter meaning that it shows values in column only if the 2 values are not null.
      However, even when I add the not blank filter on my column label, it doesn't work since one company will have at least one role with forced 2 values but not both.

       



      I hope it will be more clear with the two pictures

       

  • Zoko's avatar
    Zoko
    Frequent Visitor
    8 years ago

    Hey everyone !

    I was wondering if there are any ways to make my matrix shows only rows having values in all my columns.
    In my case i have companies names as rows and 2 columns with 2 different roles.
    One company can have either one or both roles. Plus each roles have 2 values. Thus I want all rows with 4 values

     

    I have tried to set up some filters, having both my values filtered as not blank and my role column with the same setting, however, it doesn't take into account both columns.
    I have been trying multiple things but I can't get what I am aiming for.
    Might need to change my data model.

    Thanks for help :smileyhappy: