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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors