topic Need help with DAX measure to show user defined value in row subtotals in matrix in DAX Commands and Tips

Need help with DAX measure to show user defined value in row subtotals in matrix

Anonymous — Thu, 01 Jun 2023 12:36:37 GMT

Hi All ,

I have matrix below where I am showing contact data mismtatch count based on firstname and lastname column .
Count of firstname and lastname is correct but in parent row - Contact I want to calcualte distinct of contactid where
(fistname <> "matched" or lastname <> "matched") so its basiscally not sum of firstname and lastname count 7252 (so count will be less in Contacts row ) . Similarly, Totals also would not be sum of each row (7252) . Apart from Contacts , I have some other factor as well for data mismatch which will be getting added to matrix as row .

Contacts FirstName Data Mismatch = CALCULATE(DISTINCTCOUNT(DataMismatch[CRMContactId]),KEEPFILTERS (DataMismatch[FirstNameCheck]<>"MATCHED"))

Data Mismatch =

SWITCH(SELECTEDVALUE('List of Tables'[Level1]),
"Contacts",
SWITCH (SELECTEDVALUE('List of Tables'[Level2]),
"Firstname",[Contacts FirstName Data Mismatch]+0,
"Lastname",[Contacts LastName Data Mismatch Count]+0))

Final Data Mismatch = SUMX('List of Tables',[Data Mismatch])

Please help me with solution ! Thanks in advance

Re: Need help with DAX measure to show user defined value in row subtotals in matrix

tamerj1 — Thu, 01 Jun 2023 13:03:42 GMT

Hi Anonymous
It is clear why this is hapenning but the question is what should be the correct value at the total and based on what? I would suggest tto create a third measure

Contacts Data Mismatch = CALCULATE ( DISTINCTCOUNT ( DataMismatch[CRMContactId] ), KEEPFILTERS ( DataMismatch[FirstNameCheck] <> "MATCHED" || DataMismatch[LastNameCheck] <> "MATCHED" ) )

Then the final measure to use in the visual would be

Data Mismatch = IF ( SELECTEDVALUE ( 'List of Tables'[Level1] ) = "Contacts", IF ( HASONEVALUE ( 'List of Tables'[Level2] ), IF ( VALUES ( 'List of Tables'[Level2] ) = "Firstname", [Contacts FirstName Data Mismatch] + 0, [Contacts LastName Data Mismatch Count] + 0 ), [Contacts Data Mismatch] ) )

Re: Need help with DAX measure to show user defined value in row subtotals in matrix

Anonymous — Thu, 01 Jun 2023 13:33:39 GMT

Thanks a lot tamerj1 , seems its working . I will apply the login in my actual dataset and check. thanks again!