Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Your tables weren't related. Here is what you need to do:
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:
Here is your file back so you can see it more detail what I did.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour tables weren't related. Here is what you need to do:
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:
Here is your file back so you can see it more detail what I did.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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:
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. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |