DAX measure to check whether the values in a particular field fulfils a condition

[kbansal]

Hi All,

I needed help with the a DAX measure. There are 4 columns available –

1) ID(unique identifier)

2) Markets

3) Vendors

4) Status

Think of 'Markets' like a top level branch and then 'Vendors' as sub-branches. 'Status' column either has 'Approved' or 'Rejected'

I want to create a 'Yes'/'No' DAX measure which tells me whether a particular Market has 5+ unique vendors with each vendor having 10+IDs as ‘Approved’

[kbansal]

Hi Jay,

The measure that you are proposing does not take it into account for the 10+ approved IDs. Ideally we only want to count vendors who have 10+IDs as 'Approved' Status. Can you share how to include that as well?

[Anonymous · Accepted solution]

Hi @kbansal ,

Try this measure:

count_vendors =
VAR count_id =
    CALCULATE (
        COUNT ( 'table'[id] ),
        FILTER ( ALLEXCEPT ( 'table', 'table'[vendors] ), 'table'[status] = "Approved" )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'table'[Vendors] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            'table'[Markets] = SELECTEDVALUE ( 'table'[Markets] )
                && count_id > 10
        )
    )

Best Regards,

Jay

[Anonymous]

Hi @kbansal ,

Please try below measure:

count_vendors =
CALCULATE (
    DISTINCTCOUNT ( 'table'[Vendors] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'table'[Markets] = SELECTEDVALUE ( 'table'[Markets] )
            && 'table'[Status] = "Approved"
    )
)

Then create the if measure.

IF([count_vendor]>5,"Yes","No")

Best Regards,

Jay