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

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.

Reply
SarikaKumari18
Helper III
Helper III

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 @SarikaKumari18 
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
SarikaKumari18
Helper III
Helper III

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 @SarikaKumari18 
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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors