Calculate COUNT using a measure

[ansa_naz]

I am trying to count the number of Jobs by SiteID, with potential filters being applied to Customer/Fault/Status/Lifts tables.

My tables are:

Sites

Lifts

Customers

Sales

Jobs

Fault

Status

Relationships (which cannot be amended) are:

In my Jobs table I have:

JobID	SiteID	LiftID	StatusID	FaultID
J1	S1	L1	ST01	F01
J2	S3	L5	ST01	F02
J3	S2	L3	ST04	F01
J4	S2	L3	ST03	F05
J5	S4	L6	ST02	F04
J6	S4	L9	ST03	F04
J7	S4	L9	ST04	F01
J8	S4	L9	ST04	F02
J9	S1	L2	ST05	F03
J10	S1	L1	ST05	F03
J11	S1	L1	ST04	F05
J12	S3	L4	ST02	F02
J13	S2	L3	ST02	F01
J14	S3	L4	ST03	F01
J15	S2	L3	ST01	F02
J16	S4	L8	ST02	F02
J17	S4	L9	ST04	F03
J18	S4	L8	ST03	F06
J19	S3	L5	ST02	F06
J20	S3	L5	ST02	F05
J21	S1	L1	ST02	F04
J22	S4	L7	ST04	F04
J23	S4	L8	ST03	F01
J24	S4	L7	ST03	F07
J25	S4	L8	ST01	F03
J26	S4	L9	ST01	F03
J27	S3	L4	ST02	F02
J28	S4	L6	ST03	F01
J29	S4	L7	ST04	F01
J30	S4	L6	ST05	F05

In my Lifts table I have:

LiftID	SiteID	LiftName	LiftType
L1	S1	Lift 1	Y
L2	S1	Lift 2	N
L3	S2	Lift 3	Y
L4	S3	Lift 4	N
L5	S3	Lift 5	Y
L6	S4	Lift 6	Y
L7	S4	Lift 7	N
L8	S4	Lift 8	Y
L9	S4	Lift 9	Y
L10	S5	Lift 10	N
L11	S6	Lift 11	Y
L12	S6	Lift 12	N

My Sites table is:

SiteID	CustomerID	SiteName
S1	C1	Site 1
S2	C1	Site 2
S3	C2	Site 3
S4	C3	Site 4
S5	C1	Site 5
S6	C5	Site 6

My Customers table is:

CustomerID	CustomerName
C1	Customer 1
C2	Customer 2
C3	Customer 3
C4	Customer 4
C5	Customer 5

My JobCount measure is currently:

JobCount = IF(ISBLANK(CALCULATE(COUNT(Jobs[JobID]))),BLANK(),COUNTX(ALL(Jobs),
CALCULATE(COUNT(Jobs[JobID]),FILTER(ALL(Sites),'Sites'[SiteID]=
MAX(Sites[SiteID])))))

This measure should show me a JobCount by Site, after all filters have been applied to the various tables

Example:

Page Filter - Customers 1 & 3, LiftType Y

Card Visual filter - FaultID F01&F02, StatusID ST01 & ST02 & ST03

This measure is incorrect. Without selecting any Sites in the table visual I would expect it to be 6. Selecting each site I should get (LiftName should not impact the JobCount measure)

SiteName     LiftName    Jobcount

Site 1            Lift 1          1

Site 2            Lift 3          3

Site 4            Lift 6          2

Site 4            Lift 8          2

Site 4            Lift 9          2

What I am getting is:

SiteName     LiftName    Jobcount

Site 1            Lift 1          5

Site 2            Lift 3          5

Site 4            Lift 6          14

Site 4            Lift 8         

Site 4            Lift 9          14

Any ideas how I can get this measure to just give me a job total per site, not per lift?

PBIX File

Cheers for all help

[v-xicai · Accepted solution]

Hi @ansa_naz 

You can create measure JobCount changing DAX formula to take all selected filter effective.

JobCount = IF(ISBLANK(CALCULATE(COUNT(Jobs[JobID]))),BLANK(),COUNTX(ALLSELECTED(Jobs),CALCULATE(COUNT(Jobs[JobID]),FILTER(ALL(Sites),'Sites'[SiteID]=MAX(Sites[SiteID])))))

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeCqdcxkBiZLvN3NiX...

Best Regards,

Amy

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

[v-xicai · Accepted solution]

Hi @ansa_naz 

You can create measure JobCount changing DAX formula to take all selected filter effective.

JobCount = IF(ISBLANK(CALCULATE(COUNT(Jobs[JobID]))),BLANK(),COUNTX(ALLSELECTED(Jobs),CALCULATE(COUNT(Jobs[JobID]),FILTER(ALL(Sites),'Sites'[SiteID]=MAX(Sites[SiteID])))))

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeCqdcxkBiZLvN3NiX...

Best Regards,

Amy

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

[ansa_naz]

Hi @v-xicai Any idea how I can have the card visual show the same value as the table? I have selected Site 4 in table below, which has a JobCount measure of 2 in the table, however in the card visual it shows as 1?

Also, the total should be 6, but it is showing as 2, if you can help with that too, that would be marvellous 🙂

Many thanks for the help

[ansa_naz · Accepted solution]

Worked it out, changed the FILTER function and used a ONEWAY relationship from the existing BOTH ways relationship:

FILTER (
                ALLSELECTED ( Sites ),
                'Sites'[SiteID] IN VALUES ( Sites[SiteID] )
            )
        )
    ),
    CROSSFILTER ( Jobs[LiftID], Lifts[LiftID], ONEWAY )

Cheers for the help