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 ro...
  • 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

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

      • Zoko's avatar
        Zoko
        Frequent Visitor
        8 years ago

        Hi v-yulgu-msft

        It's working good with the new measure you sent me thanks a lot !

        What i forgot to tell was that my role come from another table which is linked to my primary table containing the IDs.
        In fact, I still had the role defined in my primary table, thus I simply used them.
        But the second measure still helped me since, by using only my primary table data, a company will never have a role with null values, it only existed since I used the role from another table.

        Quite impressive that you managed to help me, despite the fact of not knowing my data model.
        Thanks a lot !