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, I would like to ask help on how I can make the result of my calculated measure as numbers and show the total.
I have 2 tables Jobs table and Date table, they have indirect relationship.
I extracted the number values on Job table "Number" column but it seems the value is not changing when I select certain year on my date slicer and the total is not showing.
Extracted Size measure =
var _number = MAX( Job[Number] )
return
IF(
CONTAINSSTRING( _number, "_" ),
RIGHT( _number, 3 )
)
Hi @tamerj1
For the Date filter here's my data model
Job table have relationship with Journal Transaction that is connected in Dates table
Hi @kristel_tulio
Please try
Extracted Size measure =
SUMX (
VALUES ( 'Job'[Number] ),
VAR _number = 'Job'[Number]
RETURN
IF ( CONTAINSSTRING ( _number, "_" ), VALUE ( RIGHT ( _number, 3 ) ) )
)
Otherwise, I would guess that some numbers contain less than 3 digits. Then it would be safe to use.
Extracted Size measure =
SUMX (
VALUES ( 'Job'[Number] ),
VAR String = 'Job'[Number]
VAR Items =
SUBSTITUTE ( String, "_", "|" )
RETURN
VALUE ( PATHITEM ( Items, 2 ) )
)
If you have even more patterns of the string then please present sample of all possibilities to write the code accrdingly.
Hi @tamerj1
The calculation is working now, just the value is not changing when selecting the date filter.
Thank you for the help!
@kristel_tulio
Sorry I just noticed your reply that contains the details about your data model.
Please try
Extracted Size measure =
SUMX (
CALCULATETABLE (
VALUES ( 'Job'[Number] ),
CROSSFILTER ( 'Job'[UID], 'Journal Transactions'[UID], BOTH )
),
VAR String = 'Job'[Number]
VAR Items =
SUBSTITUTE ( String, "_", "|" )
RETURN
VALUE ( PATHITEM ( Items, 2 ) )
)
Hi @tamerj1
Thank you for helping out, but I think it's still not calculating right. Please see the image below. I can't use as well the Job UID in Journal Transactions table because of many blank values
This is my data model
I changed the relationship in my data model since the Job UID in Journal transaction is not complete and there are more blanks.
@kristel_tulio
Please try
Extracted Size measure =
SUMX (
CALCULATETABLE (
VALUES ( 'Job'[Number] ),
USERELATIONSHIP ( 'Job'[UID], 'Journal Transactions'[UID] ),
CROSSFILTER ( 'Job'[UID], 'Journal Transactions'[UID], BOTH )
),
VAR String = 'Job'[Number]
VAR Items =
SUBSTITUTE ( String, "_", "|" )
RETURN
VALUE ( PATHITEM ( Items, 2 ) )
)
@kristel_tulio
The above solution was based on revision 2 data model wehere you had an inactive relationship between 'Job' and 'Journal Transaction' which soes not exist any more in revision 3 data model! Please confirm the final data model that you will work on before proceeding further with any solution.
@kristel_tulio
Please try
Extracted Size measure =
SUMX (
CALCULATETABLE (
VALUES ( 'Job'[Number] ),
CROSSFILTER ( 'Accounts'[UID], 'Journal Transactions'[Job UID], BOTH ),
CROSSFILTER ( 'Job'[UID], 'Job Register'[Job UID], BOTH )
),
VAR String = 'Job'[Number]
VAR Items =
SUBSTITUTE ( String, "_", "|" )
RETURN
VALUE ( PATHITEM ( Items, 2 ) )
)
Hi @tamerj1 ,
I tried placing Date in the table and filter is working but when I removed it it won't work again. And also I notice that the total works only in Job Number level but it doesn't work in date level.
to get total value please use
Extracted Size measure =
SUMX (
VALUES ( Job[Number] ),
VAR _number = Job[Number]
RETURN
IF ( CONTAINSSTRING ( _number, "_" ), RIGHT ( _number, 3 ) )
)
However, I don't gave enough information to judge why it's not filtering. Please provide more details about you data model and relationships perhaps with screenshots
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
72 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |