cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ansa_naz Member
Member

Calculate COUNT using a measure

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:

Relations.jpg

 

In my Jobs table I have:

 

JobIDSiteIDLiftIDStatusIDFaultID
J1S1L1ST01F01
J2S3L5ST01F02
J3S2L3ST04F01
J4S2L3ST03F05
J5S4L6ST02F04
J6S4L9ST03F04
J7S4L9ST04F01
J8S4L9ST04F02
J9S1L2ST05F03
J10S1L1ST05F03
J11S1L1ST04F05
J12S3L4ST02F02
J13S2L3ST02F01
J14S3L4ST03F01
J15S2L3ST01F02
J16S4L8ST02F02
J17S4L9ST04F03
J18S4L8ST03F06
J19S3L5ST02F06
J20S3L5ST02F05
J21S1L1ST02F04
J22S4L7ST04F04
J23S4L8ST03F01
J24S4L7ST03F07
J25S4L8ST01F03
J26S4L9ST01F03
J27S3L4ST02F02
J28S4L6ST03F01
J29S4L7ST04F01
J30S4L6ST05F05

 

In my Lifts table I have:

LiftIDSiteIDLiftNameLiftType
L1S1Lift 1Y
L2S1Lift 2N
L3S2Lift 3Y
L4S3Lift 4N
L5S3Lift 5Y
L6S4Lift 6Y
L7S4Lift 7N
L8S4Lift 8Y
L9S4Lift 9Y
L10S5Lift 10N
L11S6Lift 11Y
L12S6Lift 12N

 

My Sites table is:

SiteIDCustomerIDSiteName
S1C1Site 1
S2C1Site 2
S3C2Site 3
S4C3Site 4
S5C1Site 5
S6C5Site 6

 

My Customers table is:

CustomerIDCustomerName
C1Customer 1
C2Customer 2
C3Customer 3
C4Customer 4
C5Customer 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

 

JobCount1.jpg

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-xicai Senior Member
Senior Member

Re: Calculate COUNT using a measure

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])))))

 

2.png

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 Member
Member

Re: Calculate COUNT using a measure

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

3 REPLIES 3
v-xicai Senior Member
Senior Member

Re: Calculate COUNT using a measure

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])))))

 

2.png

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 Member
Member

Re: Calculate COUNT using a measure

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 Smiley Happy

 

JobCount2.jpg

Many thanks for the help

ansa_naz Member
Member

Re: Calculate COUNT using a measure

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