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
    )
)
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

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

        Yes, that does it, thanks again!