Dax measure not returning the correct number of rows

[o59393]

Hi all

I have 2 tables linked by a column called Merged.3

The dax measure I created is returning me the total number of rows from the tablle 'All' (9), however it should return a value of 6 since I am choosing 2 of the 3 names as seen below:

The desired result should be 6 because Jose and Maria both have 3 rows each from the table called 'All':

Dax:

Non Duplicate Merged3 = 

COUNTAX(
    SUMMARIZE(
        'All',
        'All'[Merged.3]),
        'All'[Merged.3]
)

I attach pbix: sample_merge_3.pbix

Can you please help me correct the dax to have the value of 6 when I choose Jose and Maria?

Thanks.

[Greg_Deckler]

@o59393 Assuming that your slicer is based off of your All table, then it should just be:

COUNTROWS('All')

If it is not, then make sure that you have a relationship between your slicer table and your 'All' table and it still should be just COUNTROWS('All')

[o59393]

Hi @Greg_Deckler 

No, the slicer needs to come from the other table.,The 'All' table is just a master table.

Can you please have a look to the pbix?

Thanks.

[Greg_Deckler]

@o59393 Switch your relationship direction to Both

[o59393]

Hi @Greg_Deckler 

Switched to both and now it shows a 3, still incorrect.

Thanks

[o59393]

Hi @Greg_Deckler 

I tried with both, but still couldnt make it work. Can you please advise?

Thanks.

[Greg_Deckler · Accepted solution]

@o59393 Switch relationship direction to Both. Switch your relationship columns to Name (for both tables). Switch your relationship type fo Many-to-Many. Use a measure that just does COUNTROWS('All'). See PBIX file below signature. BTW, this model is kind of odd, probably better to create a dimension table for your names like DISTINCT('Actual'[Name]), create a 1:* between this table and your All and Actual tables and use this table in your slicer.

[o59393]

Thanks @Greg_Deckler