Calculating values outside selected items in DAX

[miguelsus2000]

Hi,

I have a 2 tables that interact with each other, Table A and Table B.

Table A shows a person with # of relatives (e.g. brother, sister, etc)

Person      #Total Relatives  

John              3

Mary             2

Table B Lists details of each Relative

Relative               Height     Age 

John_Brother      6              21

John_Dad            5.7           52

John_Sis              5.2           17

Mary_Sis             5.2           15

Mary_Mom          5.1          48

What i want to do is select John in table A and John_Dad in table B and still be able to see in Table A that John has 3 relatives.  Instead, it only shows 1 relatives because that's what's been selected even though it has 3.

I tried this, but ican't get any where:

Total Relatives = 

calculate (

COUNTROWS('TableB'),

filter(

All ('TableA'[Relatives]),'TableA'[Person] <> BLANK()

)

)

I appreciate your help.

[miguelsus2000 · Accepted solution]

Thank you Everyone... i decided to go with a new table (using the summarize command), which worked out very well.  Thank you for the various ideas.

[VahidDM]

Hi @miguelsus2000 

You can create a measure as bellow for Relative and use that in your first table:

Relative =

CALCULATE(COUNTROWS('Table'),REMOVEFILTERS('Table'[Relative ]))

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attahced pbix file.

Total number of relatives: =
VAR currentperson =
    MAX ( TableA[Person] )
RETURN
    COUNTROWS ( FILTER ( TableB, LEFT ( TableB[Relative], 4 ) = currentperson ) )

[miguelsus2000]

Hi Jihwan... this is close, 2 observations:

1. the data in Table A and in Table B really come from the same table (let me call Table C- I expanded below). and
2. when you selected in the slicer, the smaller table shows two relatives for John and 1 for Mary.  I still want it to display 3 relatives for John and 2 for Mary no matter what the slicer selection is.  Hope this helps and thank you for your help.

Table C This is where all the data comes from . Table A and B are displayed separately on the dashboard for the user to select the data.

Person    Relative               Height     Age 

John       John_Brother      6              21

John       John_Dad            5.7           52

John       John_Sis              5.2           17

Mary       Mary_Sis             5.2           15

Mary       Mary_Mom          5.1          48

[PaulDBrown]

How do the tables interact?

[miguelsus2000]

All the data from Table A and Table B comes from the same source table, but i split them visually into A & B for data slicing & dicing on the dashboard.  So the source table looks like this, which looks mostly like Table B:

John    John_Brother  6     21

John    John_Dad       5.7   52

etc....

HOpe this answers your question.

[PaulDBrown]

If you set up the model as follows

You can set up the visual with the slicers and table with the fields from the Dimension tables and with this measure:

Number of relatives = 
CALCULATE(COUNT('Relative Table'[RelativeT]), ALL('Relative Table'[RelativeT]))

You will get

[miguelsus2000]

Hi Paul... I'm going to try this later today and get back.

Thank you..

M