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
    )
)
tamerj1's avatar
tamerj1
Community Champion
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
    )
)
YBavré's avatar
YBavré
Frequent Visitor
3 years ago

tamerj1  thnaks a lot: it does the job and I get the resut I was looking for. 

Best regards.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    YBavré 
    As per speedramps recommendation, the following should perform faster.

     

    X =
COUNTROWS (
    FILTER (
        VALUES ( ENHANCED_CKI_CUSTOMER[Enterprise_Nbr] ),
        ISEMPTY(
            EXCEPT (
                { "A", "B" },
                CALCULATETABLE ( VALUES ( ENHANCED_CKI_PRODUCTS[LOB] ) )
            )
        )
    )
)

     

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

      Hi tamerj1 , I get the error message "a table of multiple values was supplied where a single value was expected" when I run your latest measure...

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        YBavré 
        Sorry I should have used ISEPTY not ISBLANK. Updated in the reply