how do i get a measure on a card to react when I click on another table

[jimmyfromus]

Hi, 

I have 2 tables and when i click on my second table a measure on a card reacts as it should, I would however like that this card also reacts when I click on my first table. For example if I click on contact a in my first table, it should give me a value of 2 in the card visual.  I've included the sample pbix file here 

Thanks for any help. 

[edhans · Accepted solution]

Your tables weren't related. Here is what you need  to do:

1. Create the following relationships in the Model View:
3. Modify your measure as below:

Measure = 
VAR varRecordCount = 
    CALCULATE(
        DISTINCTCOUNT('Second Table'[Committee]),
        CROSSFILTER(
            company[Company Id],
            'First Table'[companyid],
            Both
        )
    )
RETURN
    COALESCE(varRecordCount,0)

This is what it does:

1. It does a Distinct Count of the committee amounts from the second table, as you had.
2. It then changes the cross-filter direction for this measure only to bi-directional between the First Table Company ID and the Company ID DIM table. NOTE: This may not be correct. It may need to change the relationship between the Contact[Contact ID] and First Table[Contact ID] - I don't know what the correct result is.
3. It then takes that value and wraps it in a COALESCE statement that says "if it is blank, return the first alternate value, which is zero. Better practice than +0. It is not faster but doesn't force a type transition from blank to numerical.

Here is your file back so you can see it more detail what I did.

[jimmyfromus]

@edhans Just wondering about the measure. If I click on contact a with membership number 26 in the first table, it should give me a value of 2 for the committee measure (dancing and running). Could you please have a look? 

You'll be pleased to know that I've changed my model to reflect a star model. 🙂

Thanks for your help. 

[edhans]

It is because your DIM tables are not set up properly. This is your contact table:

This is your "First Table"

So bidirectional filtering or not, there is filtering going on at all between the Contact and First Table. You need all contact Ids in the COntact table. When I modify your contact table to the following contents:

the below measure works perfectly.

Measure = 
VAR varRecordCount = 
    CALCULATE(
        DISTINCTCOUNT('Second Table'[Committee]),
        CROSSFILTER(
            contact[Contact Id],
            'First Table'[contactid],
            Both
        )
    )
VAR Result = COALESCE(varRecordCount,0)
RETURN
    Result

So remember your DIM tables should always have every record your FACT tables will have. It is ok to have records in your DIM table not in your FACT table. So you could have contact 99a in the contact table and nothing would change.

But if you have 99a in your fact table and not in the DIM table the following happens:

1. the contact table no longer filters the First Table for contact 99a because there is no 99a in the contact table.
2. The model will insert a blank row to handle this inconsistency. This isn't a break or a bad thing - it prevents data in your FACT table from being ignored. It just won't be filtered.

Additionally, your foreign keys in your fact tables should be hidden and not used in visuals. See the model below:

Then you remove the 'First Table'[contactid] field from the table visual and replace it with contact[Contact Id] field.

FWIW - I will be talking about this in a presentation on Feb 19 at 7am pacific. You should consider registering for the event. Lots of great content beside my Data Modeling 101 talk.

Redownload the PBIX file I linked to earlier in the thread. I've updated it with all of the changes I've made. NOTE: I did not fix the company table in a similar fashion. I leave that up to you to do. 😁

[jimmyfromus]

Thanks @edhans, very imformative. I'll be there on Tuesday. 🙂

In my case here, I would however like to have the company included in the measure so when I click on the company in table 1, the committee value changes to the correct value. Can I add 2 variables to a measure, something like this below? I'm unsure how to get both results though. Thanks again. 

Measure = 
VAR varRecordCountContact = 
    CALCULATE(
        DISTINCTCOUNT('Second Table'[Committee]),
        CROSSFILTER(
            contact[Contact Id],
            'First Table'[contactid],
            Both
        )
    )
VAR Result = COALESCE(varRecordCountContact,0)


VAR varRecordCountCompany = 
    CALCULATE(
        DISTINCTCOUNT('Second Table'[Committee]),
        CROSSFILTER(
            company[Company Id],
            'First Table'[companyid],
            Both
        )
    )
VAR Result1 = COALESCE(varRecordCountCompany,0)
RETURN
    Result

[edhans]

Feb 19 is on Friday.

At the end after return, you can just say

Result + Result1

It will add them together. I don't think it will work correctly until the company DIM table is fixed though.

[jimmyfromus]

@edhans, yes Friday. 🙂

I updated the company table, there's only value missing. It's not however behaving as I would expect. Thanks again, much appreciated. 

I've attached the file below:

The file 

[edhans]

@jimmyfromus how would you expect it to behave? You made all relationships between the company table and the first/second table inactive, so by default nothing will filter. You can activate it with USERELATIONSHIP, but that is usually only used for 2nd and subsequent relationships, or special cases.

Can you be really explicit in what you want? This thread has morphed quite a bit from the original requirements and I kind of feel like I am chasing an unspecified result here.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[jimmyfromus]

Hi @edhans, thank you but I can't create a relationship with the 2 tables in my data model. I have a filter measure currently as in the pbix file: 

Filter Measure =

VAR __selectedcontact = SELECTEDVALUE ( 'First Table'[contactid] )

VAR __selectedcompany = SELECTEDVALUE ( 'First Table'[companyid] )

RETURN

SWITCH ( TRUE(),

NOT ISBLANK ( __selectedcontact ) && __selectedcontact <> "", CALCULATE ( COUNTROWS ( 'Second Table' ), TREATAS ( { __selectedcontact }, 'Second Table'[contactid] ) ),

NOT ISBLANK ( __selectedcompany ), CALCULATE ( COUNTROWS ( 'Second Table' ), TREATAS ( { __selectedcompany }, 'Second Table'[companyid] ) ),

1

)

Is there a way by clicking in table 1 to return the distinct count of the committees without forming a relationship between the 2 tables?

Thanks again.

[edhans]

I'm not going to say no, because as soon as I do someone will post some elegant or overly complex DAX to do so. But you are trying to get Power BI to work in a way it wasn't designed. It relies on relationships for its cross-highlighting/crossfiltering functionality, and specifically needs a Star Schema, not 4 disconnected tables.

Honestly, you should really rethink why you cannot relate the DIM tables to the FACT tables.

Microsoft Guidance on Importance of Star Schema

Every single time I try to design a model that isn't fundamentally a Star Schema, it winds up biting me in the rear the further along in the report I get, and I inevitably go back and redo it in a Star Schema. 

Every. Single. Time.