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 dear community,
I know, my question is similar to that one. However, unfortunately, it did not help resolving my problem.
I got following situation:
What I want to achieve:
Sum of Budget until month where actuals are available. I achieved showing blanks. However, as you can see in the screenshot, the total sum still represents 12 months.
My DAX:
Basically, the two measures come from the same table and column; once marked as budget and once as actual.
Active Customers := CALCULATE ( SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] ); FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" ); dim_type[dty_type] = "Actual" )
Active Customers Budget Blank := IF ( ISBLANK ( [Active Customers] ); BLANK (); CALCULATE ( SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] ); FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" ); dim_type[dty_type] = "Budget" ) )
Do you know any DAX-Syntax for that?
That would be really helpful!
Cheers,
Simon
Solved! Go to Solution.
Hi @Anonymous,
Could you try the formula below to see if it works in your scenario?
Active Customers Budget Blank := IF ( ISBLANK ( [Active Customers] ); BLANK (); CALCULATE ( SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] ); FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" ); FILTER ( 'Table_With_Actual-Month_Column'; [Active Customers] <> BLANK () ); dim_type[dty_type] = "Budget" ) )
Note: Replace 'Table_With_Actual-Month_Column' with your real table name that has the "Actual-Month" column.
Regards
Hi @Anonymous,
Could you try the formula below to see if it works in your scenario?
Active Customers Budget Blank := IF ( ISBLANK ( [Active Customers] ); BLANK (); CALCULATE ( SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] ); FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" ); FILTER ( 'Table_With_Actual-Month_Column'; [Active Customers] <> BLANK () ); dim_type[dty_type] = "Budget" ) )
Note: Replace 'Table_With_Actual-Month_Column' with your real table name that has the "Actual-Month" column.
Regards
Awesome, that worked!
Thank you so much! That should help me moving on developing my tabular model.
This is the final query modified accordingly to your query:
IF ( ISBLANK ( [Active Customers] ); BLANK (); CALCULATE ( SUMX ( fact_scorecard; fact_scorecard[fsc_value_dig] ); FILTER ( 'Kanal Digital Communication'; [dme_lvl_two] = "Active Customer" ); FILTER ( Datum ; [Active Customers] <> BLANK () ); dim_type[dty_type] = "Budget" ) )
Best Regards
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 |
---|---|
116 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |