Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure to check single value

Hi Community,

 

This might be something basic, but I can't get it right.  In the following, I want to count only those clients who do not have emergency contact type.

I tried a measure : calculate(distinctcount(client),contacttype<>'Emergency')

This results in 6, however I expect 4

 

Thanks!

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    Anonymous try this measure

     

    Measure = 
COUNTROWS (
    EXCEPT (
        VALUES ('Table'[Client] ),
        CALCULATETABLE (
            VALUES ('Table'[Client] ),
            'Table'[Contact Type] = "Emergency" 
        )
    )
)

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I suggest having a table structure like below. It can be obtained in Power Query editor by using FillDown function.

    Please check the below picture and the attached pbix file.

     

     

    Expected result: =
VAR newtable =
    ADDCOLUMNS (
        VALUES ( Data[Client] ),
        "@Emergency", CALCULATE ( IF ( "Emergency" IN VALUES ( Data[Contact Type] ), "No" ) )
    )
VAR filternewtable =
    FILTER ( newtable, [@Emergency] <> "No" )
RETURN
    COUNTROWS ( filternewtable )
  • Thejeswar's avatar
    Thejeswar
    4 years ago

    Hi Anonymous ,

    Using Power QUery you can fill down the Client Column to look like below

    You can then use the below DAX to get this

    No_of_clients_no_emergency_contacts = 
VAR Noofemergencycontacts = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Contact Type] = "Emergency")
VAR NoOfallclients = DISTINCTCOUNT('Table'[Client])
RETURN
NoOfallclients - Noofemergencycontacts

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous try this measure

     

    Measure = 
COUNTROWS (
    EXCEPT (
        VALUES ('Table'[Client] ),
        CALCULATETABLE (
            VALUES ('Table'[Client] ),
            'Table'[Contact Type] = "Emergency" 
        )
    )
)

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I suggest having a table structure like below. It can be obtained in Power Query editor by using FillDown function.

    Please check the below picture and the attached pbix file.

     

     

    Expected result: =
VAR newtable =
    ADDCOLUMNS (
        VALUES ( Data[Client] ),
        "@Emergency", CALCULATE ( IF ( "Emergency" IN VALUES ( Data[Contact Type] ), "No" ) )
    )
VAR filternewtable =
    FILTER ( newtable, [@Emergency] <> "No" )
RETURN
    COUNTROWS ( filternewtable )
  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

    Using Power QUery you can fill down the Client Column to look like below

    You can then use the below DAX to get this

    No_of_clients_no_emergency_contacts = 
VAR Noofemergencycontacts = CALCULATE(DISTINCTCOUNT('Table'[Client]),'Table'[Contact Type] = "Emergency")
VAR NoOfallclients = DISTINCTCOUNT('Table'[Client])
RETURN
NoOfallclients - Noofemergencycontacts