cancel
Showing results for
Did you mean:  Helper IV

## Get count of measure = blank for measure from 2 facttables

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
1 ACCEPTED SOLUTION  Community Support

Hi @BobKoenen ,

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.  Community Support

Hi @BobKoenen ,

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.  