Updating measure

[Anonymous]

Hi

Please I need suggestions on how to solve this issue. ,

I have a table A, which has  4 columns, Locations, Trials and Interventions.  The task is to count the trials and interventions which have a United Kingdom  location. 

 Currently I have a manual workaround which relies on a filter.  And I use two cards to display the count of Trials and Interventions respectively. However this means that I have to regularly check the filter to ensure that all values with United Kingdom is selected -- see below. 

Please any suggestions on the DAX formula to  count of the number of Trials and Intervention where the

location column has a United Kingdom entry and display this value on a measure.

Thank you

PhyllisN

[pranit828]

HI @Anonymous 

I don't understand why you need two different cards.

Is the data different on both of them. If not use the same measure for both cards.

Looks like there is something missing from the ask. What is the 4th column?

Regards,

Pranit

[Anonymous]

Hi @pranit828 

Yes the data is different for both trials and intervention.

Thanks

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Just try the following two measure:

CountTrail = CALCULATE(DISTINCTCOUNT('Table'[Trials]),ALL('Table'),IFERROR(SEARCH("United Kingdom",'Table'[Locations]),-1) > 0)
CountInterventions = CALCULATE(DISTINCTCOUNT('Table'[Interventions]),ALL('Table'),IFERROR(SEARCH("United Kingdom",'Table'[Locations]),-1) > 0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dai

Thank you for your response. For some reason, I got an error message please see screen shots below.  What did I do wrong?

Thanks in advance 

[v-deddai1-msft]

Hi @Anonymous ,

I have edited my original reply. Please try again.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @pranit828 

There is a typo in the sentence. It should be 3 columns.  The client wants to display the counts on two separate cards.

Thanks

[pranit828]

Hi @Anonymous 

If the ask is display the same count on two seperate cards. Still I don't get why so? I'm puzzled.

However, use the below formula and create two cards and use the same measure in both of them.

_Count = CALCULATE(COUNTROWS('Table'),SEARCH("United Kingdom",'Table'[Locations]) > 0)

Did I resolve your issue? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

[FrankAT]

Hi @Anonymous 

this is a measure for a card:

Count trials and interventions = 
CALCULATE(
    COUNTROWS('Table'),
    LEFT('Table'[Locations],14) = "United Kingdom"
)

 Regards FrankAT

[Anonymous]

Hi @FrankAT 

Thank you for your response. However the counts for trials and interventions need to be displayed on two separate cards. 

On a different note, please explain why you have 14 in the formula.

Thank you

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the exact result you are expecting.  Also, in the image Column A is not wide enough so i cannot see that else is there in ech cell.