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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.