Forum Discussion

YBavré's avatar
YBavré
Frequent Visitor
3 years ago
Solved

Distinctcount on two filter values

Hi, I wonder if someone could help me with this issue. I have the folowing table: Enterprise_Nbr LOB (Line_of_business) 1 A 2 B 3 B 1 B 4 C 5 B 2 B 1 C   I ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi YBavré 

    please try

    X =
COUNTROWS (
    FILTER (
        VALUES ( ENHANCED_CKI_CUSTOMER[Enterprise_Nbr] ),
        COUNTROWS (
            INTERSECT (
                CALCULATETABLE ( VALUES ( ENHANCED_CKI_PRODUCTS[LOB] ) ),
                { "A", "B" }
            )
        ) = 2
    )
)
speedramps's avatar
speedramps
Super User
3 years ago

Thanks Tamerj1 for your suggestion, 

 

In this case

ISEMPTY('Table') is more efficient than COUNTROWS because it just requires one attempted read for A and one for B.

 

Whereas COUNTROWS will have to scan all the records for the context.

 

Thanks.

 

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

speedramps 

Counting the rows of a table of maximum two rows in not significant 

  • speedramps's avatar
    speedramps
    Super User
    3 years ago

    Thanks Tamerj1 

    I dont mind other Super Super offering a better solution but ISEMPTY is the the best practice for cheching if a scenario does or does not exist.

    Please dont assume there is just one record per enterprise and teach newbees to use COUNTROWS.

     

    The clue is the command names !

     

    ISEMPTY is a boolean checks if the table expression is empty

    where as COUNTROWS is computative.

     

    Please use ISEMPTY for checking and COUNTROWS for computations.

     

    No, if, buts or any excuses ... ISEMPTY is the better solution.  OK?  😀😀😀