Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Cheers for all help
Solved! Go to 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.
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |