Forum Discussion
Calculating values outside selected items in DAX
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:
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.
8 Replies
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.
How do the tables interact?
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.
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
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 ) )Hi Jihwan... this is close, 2 observations:
- the data in Table A and in Table B really come from the same table (let me call Table C- I expanded below). and
- 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
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/
