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.
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
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 |
---|---|
43 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
58 | |
50 | |
44 | |
21 | |
19 |