Forum Discussion
Helper ICountIf using two related tables
So I have done some research but still need some help. I have a main summary table that has Report ID numbers. There is a separate Issues table that may or may not contain related Report IDs. I need two counts: one where there are NOT any related records for that report ID (good reports) and then the other where there is a relationship (there is an issue with these reports). These measures will then be used on a gauge kpi.
Thanks for the information
How's something like this? ** This assume NO relation between the tables, and even easier if there is a relation. **
** Add a New Column to your Summary Table as below:
Has An Issue? =VAR Issue_Lookup = LOOKUPVALUE('Issues Table'[Report ID], 'Issues Table'[Report ID],'Summary Table'[Report ID])RETURNIF( ISBLANK( Issue_Lookup), BLANK(),1)
3 Replies
- Greg_Deckler
Community Champion
guyinazo - You can get COUNTIF equivalent here: Excel to DAX Translation - Microsoft Power BI Community
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - fhill
Resident Rockstar
How's something like this? ** This assume NO relation between the tables, and even easier if there is a relation. **
** Add a New Column to your Summary Table as below:
Has An Issue? =VAR Issue_Lookup = LOOKUPVALUE('Issues Table'[Report ID], 'Issues Table'[Report ID],'Summary Table'[Report ID])RETURNIF( ISBLANK( Issue_Lookup), BLANK(),1)
guyinazoThanks, this works and is what I needed
