Your approach to get the "Effective Quantity" for a specific "License Product Family" using DAX in Power BI seems correct. However, there are a few things that could be causing the issue:
Data Issues: Before diving deep into formulas, always ensure the data quality. Check if there's any leading or trailing white space in the "License Product Family" column values or any hidden characters. Simple errors like this can cause formulas to not match values as expected.
Formula Issues:
a. CALCULATE function recalculates an expression over a given filter. It might not give the expected result if other hidden filters are in place. In Power BI, the default behavior is to apply filters from visuals, slicers, and other measures. To clear other filters, you can use the ALL function.
ProjectLicenseCount = CALCULATE(
COUNT(LicenseSummary[Effective Quantity]),
LicenseSummary[License Product Family] = "Project Professional",
ALL(LicenseSummary)
)
b. LOOKUPVALUE function returns a single value from a column by searching for it in another column. If there are multiple matching values, it can throw an error. It's best to use this function when you are certain there's only one match.
Visualization Filters: Ensure that there aren't any filters applied at the visual, page, or report level that might be altering your results.
Data Model Relationships: Ensure that the relationships in your data model are set up correctly. Incorrect relationships can lead to unexpected filter behavior.
