topic Re: DAX query to return CompI have liant or Non Compliant based on the filter in DAX Commands and Tips

DAX query to return CompI have liant or Non Compliant based on the filter

Anonymous — Sun, 21 May 2023 12:44:48 GMT

Based on this data I want to create a calculated column or measure.

If compliance_status is both installed and required result should be Non compliant otherwise compliant in a new column.

I have no idea how to achieve it tried multiple ways but no luck.

Any suggestion how to achieve it.

Re: DAX query to return CompI have liant or Non Compliant based on the filter

Anonymous — Wed, 24 May 2023 02:05:44 GMT

Hi Anonymous,

You can try to use the following measure formula to get the correspond statues count based on current hostname:

formula = VAR currName = SELECTEDVALUE ( Table[HostName] ) VAR summary = SUMMARIZE ( FILTER ( ALLSELECTED ( Table ), [compliance_status] IN { "Installed", "Required" } ), [HostName], [compliance_status] ) RETURN IF ( COUNTROWS ( FILTER ( summary, [HostName] = currName ) ) < 2, "Compliant", "Non compliant" )

Regards,

Xiaoxin Sheng

Re: DAX query to return CompI have liant or Non Compliant based on the filter

Mrxiang — Wed, 24 May 2023 08:59:00 GMT

You can use the DAX language to create a computed column or metric to implement your requirements. Here is a sample DAX formula: Compliance Status = IF(Table1[Compliance_Status] = "Installed" && Table1[Compliance_Status] = "Required", "Non compliant", "Compliant") This formula checks whether the value in the Compliance_Status column contains both "Installed" and "Required". If so, the result is "Non compliant", otherwise it is "Compliant". You can apply this formula to a new computed column or measure to display the results in a table.

Re: DAX query to return CompI have liant or Non Compliant based on the filter

Anonymous — Sat, 27 May 2023 13:20:40 GMT

Unfortunately it didn't worked as expected.

However I tried another method and it worked.

I have created a duplicate table and then used group by to achieve the solution.