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 want to have a filter that is only applied if there is HASONEVALUE(DimCustomer[Age_Segment1_Code]) to consider all values of the customer segment (this is to ensure that when counting new customers I don't miscount customer who just changed age segment / had a birthday - as opposed to being a genuine new customer)
At the moment I have wrapped an IF around two COUNTROWS which differ only by applying ALL(DimCustomer[Age_Segment1_Code]) in the case where there is HASONEVALUE returns TRUE.
IF ( HASONEVALUE ( DimCustomer[Age_Segment1_Code] ), COUNTROWS ( FILTER ( VALUES ( FactAccount[Customer_Id] ), FactAccount[Customer_Id] IN CALCULATETABLE ( VALUES ( FactAccount[Customer_Id] ), FILTER ( ALL ( DimDate ), IF ( MonthYear, DimDate[Year] = APreviousYear && DimDate[Month] = APreviousMonth, DimDate[Year] + 1 = CurrentYear ) ), ALL ( DimCustomer[Age_Segment1_Code] ) ) ) ), COUNTROWS ( FILTER ( VALUES ( FactAccount[Customer_Id] ), FactAccount[Customer_Id] IN CALCULATETABLE ( VALUES ( FactAccount[Customer_Id] ), FILTER ( ALL ( DimDate ), IF ( MonthYear, DimDate[Year] = APreviousYear && DimDate[Month] = APreviousMonth, DimDate[Year] + 1 = CurrentYear ) ) ) ) ) )
Is there a way of integrating it into the filter itself? (the part in red) :
COUNTROWS ( FILTER ( VALUES ( FactAccount[Customer_Id] ), FactAccount[Customer_Id] IN CALCULATETABLE ( VALUES ( FactAccount[Customer_Id] ), FILTER ( ALL ( DimDate ), IF ( MonthYear, DimDate[Year] = APreviousYear && DimDate[Month] = APreviousMonth, DimDate[Year] + 1 = CurrentYear ) ), IF (HASONEVALUE ( DimCustomer[Age_Segment1_Code] ), ALL ( DimCustomer[Age_Segment1_Code] ), DimCustomer[Age_Segment1_Code] ) ) ) )
When I try this option I get the following error:
Cannot convert value '*' of type Text to type True/False
Suggestions welcome!
Covering 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 |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |