The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
All,
Having an issue with a measure aggregating to blank in a table visualization and I'm not sure why.
Specifically, I am trying to create a report that shows the current period sales in local currency for each subsidiary, then those values converted to US Dollars based on a rate table. We are getting conversion rates from this rate table using the LOOKUPVALUE function, where 2 of the 4 values are driven by slicers and the remaining 2 are driven by context in the table visualization. The table shows the subsidiary, the currency, the sales amount in local currency, the conversion rate and this new measure.
The calculations for each subsidary are working as expected and interacting with the slicers appropriately. The problem is that the new measure isn't totalling...just showing a blank.
I'm thinking this has to do with blanks and how I handle them. For example, there are subsidiaries where the local sales amount for the current period is blank (not zero but blank).
Here's the DAX:
Solved! Go to Solution.
Resolved it with a SUMX iterating over the subsidiary table.
Resolved it with a SUMX iterating over the subsidiary table.
Hi @thx1137 ,
I create simple samples and you can check the results as follows:
So your measure should also add <alternateResult>
VAR Exchange_Rate = LOOKUPVALUE(
'Exchange Rate Lookup'[Rate Value],
'Exchange Rate Lookup'[Period], PeriodValue,
'Exchange Rate Lookup'[Rate Type], Rate_Type,
'Exchange Rate Lookup'[Subsidiary], SubValue,
'Exchange Rate Lookup'[Currency], CurrValue,
0
)
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Adding 0 as the alternate value for the LOOKUPVALUE statement results in the measure aggregating to 0 across all subsidiaries. We're instead looking to have the measure aggregate to the sum of sales across all subsidiaries.
Install DAXDebugOutput and then learn how to use EVALUATEANDLOG to debug your code. You will be able to see exactly where your result turns BLANK() and can correct it as needed.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |