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.
How can I create a DAX measure to display the most frequent non-compliance outcome for a given month on a card? The measure should exclude rows with "Y" in the compliance column and "NA" or 'Nil' in the non-compliance category column. In case of ties, the measure should use severity as the tiebreaker (Low, Medium, High - assume 1,2 3 if helps?).
Initially assumed it would be straightforward, I have spent hours experimenting with various approaches involving filters, calculated tables, addcolumns, summarize, and rankx functions. Now i'm just more confused and going in circles.
Table: Compliance
The desired outcome for March 2023, the card would display "Fraud".
Any assistance or guidance would be greatly appreciated. Thank you in advance.
Solved! Go to Solution.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Expected outcome measure: =
VAR _t =
ADDCOLUMNS (
FILTER (
Compliance,
Compliance[Compliance] <> "Y"
&& NOT ( Compliance[Non Compliance] IN { "NA", "Nil" } )
),
"@tiebreaker",
SWITCH (
TRUE (),
Compliance[Severity] = "High", 3,
Compliance[Severity] = "Medium", 2,
Compliance[Severity] = "Low", 1
)
)
VAR _grouptable =
GROUPBY (
_t,
Compliance[Date],
Compliance[Non Compliance],
"@count", SUMX ( CURRENTGROUP (), 1 ),
"@tiebreak", SUMX ( CURRENTGROUP (), [@tiebreaker] )
)
VAR _highest =
MAXX (
ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
[@value]
)
RETURN
IF (
HASONEVALUE ( Compliance[Date] ),
CONCATENATEX (
FILTER (
ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
[@value] = _highest
),
Compliance[Non Compliance],
", "
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This solution is perfect, thank you so much 🙂
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Expected outcome measure: =
VAR _t =
ADDCOLUMNS (
FILTER (
Compliance,
Compliance[Compliance] <> "Y"
&& NOT ( Compliance[Non Compliance] IN { "NA", "Nil" } )
),
"@tiebreaker",
SWITCH (
TRUE (),
Compliance[Severity] = "High", 3,
Compliance[Severity] = "Medium", 2,
Compliance[Severity] = "Low", 1
)
)
VAR _grouptable =
GROUPBY (
_t,
Compliance[Date],
Compliance[Non Compliance],
"@count", SUMX ( CURRENTGROUP (), 1 ),
"@tiebreak", SUMX ( CURRENTGROUP (), [@tiebreaker] )
)
VAR _highest =
MAXX (
ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
[@value]
)
RETURN
IF (
HASONEVALUE ( Compliance[Date] ),
CONCATENATEX (
FILTER (
ADDCOLUMNS ( _grouptable, "@value", [@count] * 10 + [@tiebreak] ),
[@value] = _highest
),
Compliance[Non Compliance],
", "
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |