Extracted measures to convert as numbers and show total

[kristel_tulio]

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 )

)

[kristel_tulio]

Hi @tamerj1 

For the Date filter here's my data model

Job table have relationship with Journal Transaction that is connected in Dates table

[kristel_tulio]

Hi @tamerj1 

Thanks for replying but the calculation won't work.

[tamerj1]

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.

[kristel_tulio]

Hi @tamerj1 

The calculation is working now, just the value is not changing when selecting the date filter.

Thank you for the help!

[tamerj1]

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

[kristel_tulio]

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

[kristel_tulio]

@tamerj1 

I changed the relationship in my data model since the Job UID in Journal transaction is not complete and there are more blanks.

[tamerj1]

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

[tamerj1]

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

Hi @tamerj1 ,

This is the final data model

[tamerj1]

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

[kristel_tulio]

Hi @tamerj1 

Thank you but It seems the total is still not working

[kristel_tulio]

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.

[tamerj1]

Hi @kristel_tulio 

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