Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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 .

SarikaKumari18_0-1685622555395.png

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.