Forum Discussion

Ncio's avatar
Ncio
Icon for Helper II rankHelper II
4 years ago

Counting rows without a relationship in another column

Hello,

 

I have dax measure that seems to be working, but when I check the detail, I'm not really sure :

 

Here is a simplified version:

 

The total count seems to be correct, but the detail on each line is empty :

  • Only for one of the measure (but it's almost the same, only the "not" is different"
  • And only when I add a column from Table 2.

==> Why is that? Can I be confident that the total will be ok?

 

The queries:

Available table 2 = 
CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        not(
            ISEMPTY(
                RELATEDTABLE(Table2)
            )
        )
    )
)
Row missing in table 2 = CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        ISEMPTY(
            RELATEDTABLE(Table2)
        )
))

 

Thank you very much

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

     

    I used intersect and it lkook like ok.

    Measure 1 :

    Measure 2

    Not in table 2 =

    var _step1=SUMMARIZE(table2,Table2[Colonne 1])
    var _step2=INTERSECT(_step1,table1)
    var result=COUNTROWS(_step2)
    return
    if(result=0,1)
     
    Hope it helps !
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Ncio ,

     

    There is something strange, when these two tables are not related, the fields [rows missing in table2] should all show empty at this time, how did you get this result? 

     

    The result of the total is affected by the row context in which the measure is placed. Since the values of table2.colmun1 and table1.colmun1 do not correspond, there will be a Cartesian product. 

    Power BI allows violations of referential integrity to occur, and the system automatically maps these mismatched records to blank rows. The total of 2 here are the summary counts of these blank rows.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data