Power BI DAX - Need help in displaying Measure with Values and Text

Question

I'm trying to display a measure value with text in Card Visual. For instance, I'd like to display all IDs with HasWrongTitle = True by Department (Slicer). Based on the below data, I'd like to show "2 of 4" without slicer selection and "1 of 4" with slicer set to IT or Finance.

The below measure works partially but displays the same value for total as well.

MeasureWrongTitle = 
IF(
    ISBLANK(
        CALCULATE(
            DISTINCTCOUNT(Table[ID]),
            FILTER(
                Table,
                Table[HasWrongTitle]="True"
            ) 
        )
    ),
    "0 of " & 
    COUNTROWS(
        SUMMARIZE(
            Table,
            Table[ID]
        )
    ),

    CALCULATE(
        DISTINCTCOUNT(Table[ID]),
        FILTER(
            Table,
            Table[HasWrongTitle]="True"
        )
    ) & " of " & COUNTROWS(SUMMARIZE(Table,Table[ID]
    )
)

Table:

ID  Name        Department HasDirectReport  HasWrongTitle
100 John Doe1   Admin      True             False
101 John Doe2   IT         False            True
102 John Doe3   HR         True             False
103 John Doe4   Finance    True             True

mkRabbani mkRabbani · Accepted Answer · 2020-09-10T05:38:07

You can try this below measure with your logic but in simplified way-

MeasureWrongTitle = 

var has_wrong_title_count =
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        'Table',
        'Table'[HasWrongTitle] = TRUE() 
        //-- User ""True" if your column is string type
    ) 
)

var total_id = DISTINCTCOUNT('Table'[ID])

RETURN IF(has_wrong_title_count = BLANK(),0,has_wrong_title_count) & " of " & total_id

Here is the output using your data-

Power BI DAX - Need help in displaying Measure with Values and Text

1 Answers