Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BobKoenen
Helper IV
Helper IV

Get count of measure = blank for measure from 2 facttables

Hi all, 

 

My data looks like this 
Dim table Customer

NameHas partner
Customer AN
Custumer BJ
Customer CJ
Customer DJ

Fact table 1

NameAmount Outstanding
Customer A500
Customer B500
Customer C100
Customer D1500

Fact table 2 

NameAmount due
Customer A200
Customer B500
Customer C200
Customer D400


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
MeasureCount over all customers
Customer is valid2
Other measure4
Other measure 21
Where if I select the measure [Customer is valid] in another table wil show the valid customers:
NameTekst
Customer BCustomer is Valid
Customer DCustomer is Valid

Could you help me how to acchieve this 
1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @BobKoenen ,

Please try below steps:

1.  I add a new table

Table3:

vbinbinyumsft_0-1663830456096.png

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

Animation17.gif

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.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

Hi @BobKoenen ,

Please try below steps:

1.  I add a new table

Table3:

vbinbinyumsft_0-1663830456096.png

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

Animation17.gif

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.