Get count of measure = blank for measure from 2 facttables

[BobKoenen]

Hi all, 

My data looks like this 
Dim table Customer

Name	Has partner
Customer A	N
Custumer B	J
Customer C	J
Customer D	J

Fact table 1

Name	Amount Outstanding
Customer A	500
Customer B	500
Customer C	100
Customer D	1500

Fact table 2 

Name	Amount due
Customer A	200
Customer B	500
Customer C	200
Customer D	400

I have made serveral Measures to use in an end measure which returns a teks value or a BLANK():

MHas partner = MAX('Dim Customer'[Has partner])

Sum Due = CALCULATE(SUM(due[Amount Due]))

Sum Outstanding = CALCULATE(SUM(Outstanding[Amount]))

Valid Customer = 

IF([MHas partner] = "n" && [Sum Due] >200 && [Sum Outstanding] >200,
"This customer is valid",
BLANK())

In a table this returns per customer the text : This customer is valid" or a blank. 
However I want to calculate the total Number of customers which are valid. And be able to filter on this to get only the valid Customers. 

My dashboard would look like this: 
A table with measures

Measure	Count over all customers
Customer is valid	2
Other measure	4
Other measure 2	1

Where if I select the measure [Customer is valid] in another table wil show the valid customers:

Name	Tekst
Customer B	Customer is Valid
Customer D	Customer is Valid

Could you help me how to acchieve this 

[v-binbinyu-msft · Accepted solution]

Hi @BobKoenen ,

Please try below steps:

1.  I add a new table

Table3:

2. create some measures with below dax formula

Customer is Valid =
VAR cur_name =
    SELECTEDVALUE ( Customer[Name] )
VAR cur_ptn =
    SELECTEDVALUE ( Customer[Has partner] )
VAR cur_out =
    SELECTEDVALUE ( Table1[Amount Outstanding] )
VAR cur_due =
    SELECTEDVALUE ( Table2[Amount due] )
RETURN
    IF (
        cur_ptn <> "N"
            && cur_due > 200
            && cur_out > 200,
        "Customer is valid",
        BLANK ()
    )

Other =
VAR cur_name =
    SELECTEDVALUE ( Customer[Name] )
VAR cur_ptn =
    SELECTEDVALUE ( Customer[Has partner] )
VAR cur_out =
    SELECTEDVALUE ( Table1[Amount Outstanding] )
VAR cur_due =
    SELECTEDVALUE ( Table2[Amount due] )
RETURN
    IF (
        cur_ptn = "N"
            && cur_due < 500
            && cur_out > 200,
        "Customer is not valid",
        BLANK ()
    )

Count over all customers =
VAR cur_measure =
    SELECTEDVALUE ( Table3[Measure] )
VAR tmp1 =
    FILTER ( ALL ( Customer ), [Customer is Valid] = "Customer is valid" )
VAR tmp2 =
    FILTER ( ALL ( Customer ), [Other] = "Customer is not valid" )
RETURN
    SWITCH (
        cur_measure,
        "Customer is Valid", COUNTROWS ( tmp1 ),
        "Other", COUNTROWS ( tmp2 )
    )

Customer is Valid =
VAR cur_name =
    SELECTEDVALUE ( Customer[Name] )
VAR cur_ptn =
    SELECTEDVALUE ( Customer[Has partner] )
VAR cur_out =
    SELECTEDVALUE ( Table1[Amount Outstanding] )
VAR cur_due =
    SELECTEDVALUE ( Table2[Amount due] )
RETURN
    IF (
        cur_ptn <> "N"
            && cur_due > 200
            && cur_out > 200,
        "Customer is valid",
        BLANK ()
    )

Tekst =
VAR cur_measure =
    SELECTEDVALUE ( Table3[Measure] )
RETURN
    SWITCH (
        cur_measure,
        "Customer is Valid", "Customer is valid",
        "Other", "Customer is not valid"
    )

Condition =
VAR cur_measure =
    SELECTEDVALUE ( Table3[Measure] )
VAR cur_name =
    SELECTEDVALUE ( Customer[Name] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( Customer[Name] ),
        FILTER ( ALL ( Customer ), [Customer is Valid] = cur_measure )
    )
VAR tmp2 =
    CALCULATETABLE (
        VALUES ( Customer[Name] ),
        FILTER ( ALL ( Customer ), [Other] = "Customer is not valid" )
    )
RETURN
    SWITCH (
        cur_measure,
        "Customer is Valid", IF ( cur_name IN tmp, 1 ),
        "Other", IF ( cur_name IN tmp2, 1 )
    )

3. i achieve your demand by drill through

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.