fitler in calculate not returning accurate aggregation on filtered data only

[tsingla]

Hi,

I am new to powerbi and started using the tool only ~2 weeks ago. I am on Mac and my company did not approve for virtual machine. Developing in powerbi service. So very limited learning material for me. I simply trying to countdistinct accounts where retention_status flag = Retained. Below DAX code. but it is returning data without filter "Retained" working on it. See code below. Addressable accounts is coming out to be same as retained accounts. I have checked there are not filters applying to visual. Where am i going wrong. What am i missing? There are no spaces in retention_status data. 

addressable_accounts = DISTINCTCOUNT(tsingla_non_msp_corp_retention_analysis_fy2023_aug3[ACCOUNTID])

retained_accounts = CALCULATE(DISTINCTCOUNT(tsingla_non_msp_corp_retention_analysis_fy2023_aug3[ACCOUNTID]), FILTER(datalake_core_sam_account_scores_history, datalake_core_sam_account_scores_history[RETENTION_STATUS]="Retained"))

[tsingla]

Aha! i had incorrect table linked to filter function. Rookie mistake!! Thank you. Any recommendations of source of material for comprehensive knowlege ? Book name, website name etc. ? Thanks again so much!

[edhans]

Matt Allington has a great book called Super charge Power BI. I work at P3 Adaptive and we have a 3 day course we deliver monthly. 

SQLBI's website is fantastic, but it isn't set up like a course, so hundreds of great articles on specific things, not a "how to get started with Power BI" layout.

[edhans · Accepted solution]

You'd need to show us the model. But the datalake_core_sam_account_scores_history table isn't filtering the tsingla_non_msp_corp_retention_analysis_fy2023_aug3 table. 

Also, a better measure most of the time would be this:

test =
CALCULATE (
    DISTINCTCOUNT ( tsingla_non_msp_corp_retention_analysis_fy2023_aug3[ACCOUNTID] ),
    datalake_core_sam_account_scores_history[RETENTION_STATUS] = "Retained"
)

That is only filtering 1 column, [RETENTION_STATUS] not the entire table. As a rule, never filter a table. Filter a column. 

But that still won't work if the table Retention Status is in a table that is filtering your fact table.

Welcome to Power BI! I would strongly encourage you to take a course or get a comprehensive beginner book. There is a lot to Power BI that can only come from "book knowledge." There are things happening under the hood as it relates to filter context, row context, context transition, and how some functions work - CALCULATE() is a very powerful function that goes way beyond how you used it  - that you cannot figure out just by playing around with data. Very much unlike Excel. You can see what each function does and grasp what is going on. You cannot do that with DAX.

You also cannot get this comprehensive knowledge in a systematic way for free. It is expensive to generate this content. Most free stuff explains, perhaps really well, one thing. No free stuff gives you a comprehensive explanation of how to build models properly, why you make your fact tables as narrow as possible, and much more. That is 16-24 hrs of a class easily.