Calculate count if the values are double

[Anonymous]

Hi Team,

Below is my table, I want to use Agency and Teams as Slicer and need count of Status in Bar Graph.

If I use agency as In Slicer.

My slicer should look something like this

SWZ

AEZ

FZ

Teams as Slicer

HR

FIN

GS

Slicer Country

IND

UK

US

My staus should not get multipled if I split the cells. 3 completed 1 inprogess 2 delayed

If the user selects UK from Slicer he usled be able to see

Agency	Country	Project	Sub Project	Teams	Status
SWZ, AEZ	IND,UK, US	A	AA	HR, FIN,GS	Completed
SWZ, AEZ	IND,UK, US	A	AB	HR, FIN,GS	In Progress
SWZ, AEZ	IND,UK, US	A	AC	HR, FIN,GS	Delayed
SWZ, AEZ,FZ	US, UK	B	BA	GS	Completed
SWZ, AEZ,FZ	US, UK	B	BB	GS	Completed
SWZ, AEZ,FZ	US, UK	B	BC	GS	Delayed

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You will need to create three slicer tables for agency, teams and country.

Then use CONTAINSSTRING() function to mark the records that contain selected value from slicers.

measure = if(containsstring(selectedvalue('Table'[Country]),selectedvalue(country[country])),1,0)

At last use SUMX() function to get the count of records for each status.

Measure 2 = SUMX(ALLEXCEPT('Table','Table'[Status]),[measure])

Best Regards,

Jay