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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors