Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
I have 2 tables:
and
there is an non-active relationship between MainUnitID and UnitID.
I use for the count of active contracts: CALCULATE( COUNTROWS(tblContract),
IF(AND(tblContract[DateContractFrom]<=Max(dates[date]),OR(tblContract[DateContractTo] >= Max(dates[date]), tblContract[DateContractTo]= 0)), 1,0))
I use for the count of active Units: CALCULATE(COUNTROWS(tblUnit), IF(AND(tblUnit[ExploitationDateFrom]<=Max(dates[date]),OR(tblUnit[ExploitationDateTo] >= Max(dates[date]), tblUnit[ExploitationDateTo]= 0)), 1,0))
I would like to count the free units (units that has no contract)
In this example is the outcome 1 (nr. 17611) on the max(dates[date]) Max(dates[date]) is here today
I would like to count the units with contract
In this example is the outcome 2 (nr. 17671 and 17650) on the max(dates[date]) Max(dates[date]) is here today
Can anyone help me?
Solved! Go to Solution.
Hi @DjZijlstra ,
This is because I did not de-duplicate the TbIContract[MainUnitID] column, please try the modified measure:
count the units with contract =
VAR tab_contract =
FILTER (
TbIContract,
AND (
TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
OR (
TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
TbIContract[DateContractTo] = 0
)
)
)
VAR tab_units =
FILTER (
TbIUnits,
AND (
TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
OR (
TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
TbIUnits[ExploitationDateTo] = 0
)
)
)
RETURN
CALCULATE (
DISTINCTCOUNT(TbIContract[MainUnitID]),
tab_contract,
tab_units,
USERELATIONSHIP ( TbIUnits[UnitID], TbIContract[MainUnitID] )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DjZijlstra ,
Please try the following measures:
count of active contracts =
CALCULATE (
COUNTROWS ( TbIContract ),
FILTER (
TbIContract,
AND (
TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
OR (
TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
TbIContract[DateContractTo] = 0
)
)
)
)
count of active Units =
CALCULATE (
COUNTROWS ( TbIUnits ),
FILTER (
TbIUnits,
AND (
TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
OR (
TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
TbIUnits[ExploitationDateTo] = 0
)
)
)
)
count the units with contract =
VAR tab_contract =
FILTER (
TbIContract,
AND (
TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
OR (
TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
TbIContract[DateContractTo] = 0
)
)
)
VAR tab_units =
FILTER (
TbIUnits,
AND (
TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
OR (
TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
TbIUnits[ExploitationDateTo] = 0
)
)
)
RETURN
CALCULATE (
COUNTROWS ( TbIContract ),
tab_contract,
tab_units,
USERELATIONSHIP ( TbIUnits[UnitID], TbIContract[MainUnitID] )
)
free units = [count of active Units] - [count the units with contract]
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-kkf-msft
That kind of measure i had to try earlier, but that was not the number i was looking for.
I have a file added (table copied below), where 1 unit has 2 contracts, and a unit has 2 contracts (1 active and 1 not active)
So if i count the contracts with these measures there a 3 contracts and 3 units, so 0 free. But thats not correct. There is 1 free.
What measure can i use to get this number?
ContractBusinessUnitIDContractNoCustomerIDContactIDMainUnitIDDateContractFromDateContractToObjectIDActief
17623 | 286 | 25263 | 19574 | 148982 | 17671 | 8-3-2022 | null | 2598836 | 1 |
17579 | 286 | 25237 | 19548 | 153272 | 17650 | 22-2-2022 | null | 2586027 | 1 |
17326 | 286 | 25118 | 19282 | 150811 | 17611 | 22-12-2021 | 25-3-2022 | 2543890 | 0 |
17624 | 286 | 25264 | 19575 | 148983 | 17671 | 8-3-2022 | null | 2598836 | 1 |
17625 | 286 | 25265 | 19575 | 148984 | 17650 | 22-2-2021 | 21-2-2022 | 2586027 | 0 |
Hi @DjZijlstra ,
This is because I did not de-duplicate the TbIContract[MainUnitID] column, please try the modified measure:
count the units with contract =
VAR tab_contract =
FILTER (
TbIContract,
AND (
TbIContract[DateContractFrom] <= MAX ( 'dates'[date] ),
OR (
TbIContract[DateContractTo] >= MAX ( 'dates'[date] ),
TbIContract[DateContractTo] = 0
)
)
)
VAR tab_units =
FILTER (
TbIUnits,
AND (
TbIUnits[ExploitationDateForm] <= MAX ( dates[date] ),
OR (
TbIUnits[ExploitationDateTo] >= MAX ( dates[date] ),
TbIUnits[ExploitationDateTo] = 0
)
)
)
RETURN
CALCULATE (
DISTINCTCOUNT(TbIContract[MainUnitID]),
tab_contract,
tab_units,
USERELATIONSHIP ( TbIUnits[UnitID], TbIContract[MainUnitID] )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
84 | |
72 | |
71 | |
67 | |
56 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |