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,

 

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 !