Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Measure | Count over all customers |
Customer is valid | 2 |
Other measure | 4 |
Other measure 2 | 1 |
Name | Tekst |
Customer B | Customer is Valid |
Customer D | Customer is Valid |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |