topic Re: Extracted measures to convert as numbers and show total in DAX Commands and Tips

Extracted measures to convert as numbers and show total

kristel_tulio — Mon, 30 Jan 2023 00:17:16 GMT

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 )

)

Re: Extracted measures to convert as numbers and show total

tamerj1 — Mon, 30 Jan 2023 03:46:13 GMT

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

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Mon, 30 Jan 2023 05:53:47 GMT

Hi tamerj1

Thanks for replying but the calculation won't work.

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Mon, 30 Jan 2023 06:09:01 GMT

Hi tamerj1

For the Date filter here's my data model

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

Re: Extracted measures to convert as numbers and show total

tamerj1 — Mon, 30 Jan 2023 07:35:58 GMT

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.

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Mon, 30 Jan 2023 23:40:38 GMT

Hi tamerj1

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

Thank you for the help!

Re: Extracted measures to convert as numbers and show total

tamerj1 — Tue, 31 Jan 2023 05:15:38 GMT

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

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Tue, 31 Jan 2023 07:13:12 GMT

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.

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Tue, 31 Jan 2023 07:17:23 GMT

tamerj1

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

Re: Extracted measures to convert as numbers and show total

tamerj1 — Tue, 31 Jan 2023 07:50:38 GMT

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

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Wed, 01 Feb 2023 05:11:45 GMT

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

Re: Extracted measures to convert as numbers and show total

tamerj1 — Wed, 01 Feb 2023 06:44:43 GMT

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.

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Wed, 01 Feb 2023 07:02:41 GMT

Hi tamerj1 ,

This is the final data model

Re: Extracted measures to convert as numbers and show total

tamerj1 — Wed, 01 Feb 2023 08:23:50 GMT

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

Re: Extracted measures to convert as numbers and show total

kristel_tulio — Thu, 02 Feb 2023 04:12:51 GMT

Hi tamerj1

Thank you but It seems the total is still not working