Performance / Size implications of BLANK() vs 0
Hello everyone, I have a question concerning how BLANKS are handled in Semantic models. I have a DAX table that I'm creating using UNION and select tables (Yes, I have to do it in DAX and not in M). In some cases, my the columns in my "Selectcolumns" need to be empty or set to 0 (Double). Is there an reason to choose one over the other ? From what I understand from this article : https://www.sqlbi.com/articles/blank-handling-in-dax/ Both 0 and Blank seem to be handled the same way; as in "BLANK" is converted to 0 during compute. By using Blank I thought there would be less memory usage, but I don't really see it. In fact, when I use the VertipackAnalyser in DAX Studio, sometimes leaving a column full of 0 is better than putting blanks everywhere. Is BLANK() stored differently than 0.0 in a semantic model ? In a SUM, are BLANK rows skipped, or are they still converted to 0 ? Has someone ever done the experiment ? At which point does this question really start to matter ?
COUNT/COUNTROWS return 0 instead of blank
Hello, I am currently have an issue with count/countrows. When they evaluate and return nothing it shows blank instead of 0. I have 2 tables, a calendar table and a fact table with 10 rows. I am trying to calculate the percentage of colum ORIT = "Y". Lines ORIT2 = var _orit = CALCULATE(COUNTROWS(dataVN),dataVN[ORIT]="Y") return if(ISBLANK(_orit),0,_orit) The result is this. I don't want those extra lines that mean nothing. The manufacturing data field comes from the calendar table. How can I avoid this situation and get the desired result? The .pbix file is in this link if you want to try - https://we.tl/t-bHGwtdUf5d Thank you. André
Relationship issues showing BLANK values
Hi All, I built a quick model with a lot of rows (10 million). The model analyses the YOY variances per product product group. A product reference is linked to a product group. In some cases, the data might have mistakes and 1 reference is linked to multiple product groups. For some reasons, I can see some "(blank)" values and I don't understand why. When I click on the revenue, it shows nothing as if it is empty. Could you please help? Thanks so much for your assistance!
delete values of a measure when a value of an other measure is blank
Hello, I wrote this measure to calculate sales on square meters Sold/Sqm = Sold/SqMeters but when I try to average the total of this measure by Store, he gets the SqMeters total wrong because he returns the SqMeters value even when the Sold value related to a Store, is empty. Store is a column Sold is a measure SqMeters is a column Sold/SqM is a measure Red is the wrong total, and green is the right onw that i want because doesn't count FOOD store that has empty sold. How do I change the measure so that it only counts the Solds present related to Store? I also wrote Sold/Sqm = VAR _soldsq = Sold/SqMeters RETURN IF([Sold],_soldsq) or Sold/Sqm = VAR _soldsq = Sold/SqMeters RETURN IF([Sold]>1,_soldsq) or Sold/Sqm = VAR _soldsq = Sold/SqMeters RETURN IF(NOT(ISBLANK([Sold]>1)),_soldsq) but it doesn't work Thank you!
Totals in pivot table excluding certain rows under condition
Hello, I would like to create a DAX measure which will calculate booking value as sum of ending backlog + sum of shipments - sum of beginning backlog, but only in case there will values of beginning and ending backlog in pivot table. In case there will be only beginning backlog value, then the booking will be 0 or blank. Total value of booking column will be calculated in the same logic - so in case the ending backlog of certain row will be missing, then the whole row will be calculated as 0. See the printscreen with final expected result I need in Power BI visualisation: I enclose dataset below as well. Thank you very much in advance. Best regards, Tomas BeginningBacklog EndingBacklog Fiscal_Year Fiscal_Week_Num Source PROD_LINE Coverage$ ITEM_NUMBER WAREHOUSE CUSTOMER_NAME FY-FW 5/4/2024 5/11/2024 2024 19 Shipments 1759 - OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 47 U1 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 - T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,907 T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 715 S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,192 S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 1,143 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 8,453 U1 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 - S EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 7,430 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 3,242 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 339 U2 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1759 408 U2 EMEA C4 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 417 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 7,295 T EMEA C3 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1759 8,988 OP EMEA C5 2024-19 5/4/2024 5/11/2024 2024 19 Ending Backlog 1760 1,928 Q APAC C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 17,697 U3 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 332 U3 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 415 OP APAC C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 27,802 Q APAC C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 - OP KOREA C5 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 16,823 U1 APAC C4 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 497 OP EU C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 297 OP EU C5 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1752 276 A EU C1 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1760 7,815 Q KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 1,790 Q KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1759 6,587 EE KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Beginning Backlog 1753 8,881 EE KOREA C2 2024-19 5/4/2024 5/11/2024 2024 19 Shipments 1753 15,431 EE KOREA C2 2024-19 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 2,017 OP EU C5 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 380 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 1,190 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 669 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 551 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 950 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 69 B EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 159 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 8 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Beginning Backlog 1752 1,078 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 950 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 69 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 380 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 159 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 669 T EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 8 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,078 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,190 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 551 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,848 EE EU C2 2024-20 5/11/2024 5/18/2024 2024 20 Shipments 1752 1,319 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 430 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 12 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,274 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 734 S EU C3 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,353 EE EU C2 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,058 U2 EU C4 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,331 U1 EU C4 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,383 A EU C1 2024-20 5/11/2024 5/18/2024 2024 20 Ending Backlog 1752 1,266 B EU C1 2024-20 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 380 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,190 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 669 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 551 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 950 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 69 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 159 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 8 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,078 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,319 B EU C1 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 12 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 430 S EU C3 2024-21 5/18/2024 5/25/2024 2024 21 Beginning Backlog 1752 1,848 EE EU C2 2024-21 5/18/2024 5/25/2024 2024 21 Shipments 1752 1,383 B EU C1 2024-21
Matrix Values Reflecting Blank Instead of 0
I have a Power BI Matrix with Rows comprised of PHA_CD_NM and the DVLPT_NUM within each PHA_CD_NM. I have three separate measures I’ve included as columns within the Matrix. The problem is that Average Vacant Days (DDA) and Average Vacant Days (Non DDA) are calculating as blank values instead of 0 (when applicable). Can anyone help me adjust these formulas so blank items appear as 0? The formulas for each measure are below. Average Vacant Days = VAR AvgVacDays = DIVIDE(SUM('F_T_UNIT'[VAC_DAYS]), SUM('F_T_UNIT'[UNIT_CNT])) VAR IsBlankAvgVacDays = ISBLANK(AvgVacDays) RETURN IF( IsBlankAvgVacDays, IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDays ), BLANK() ), AvgVacDays ) Average Vacant Days (DDA) = VAR AvgVacDaysDDA = CALCULATE( [Average Vacant Days], 'F_T_UNIT'[UNIT_DDAPP_INDR] = "Y" ) VAR IsBlankAvgVacDaysDDA = ISBLANK(AvgVacDaysDDA) RETURN IF( IsBlankAvgVacDaysDDA, IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDaysDDA ), BLANK() ), AvgVacDaysDDA ) Average Vacant Days (Non DDA) = VAR AvgVacDaysNonDDA = CALCULATE( [Average Vacant Days], 'F_T_UNIT'[UNIT_DDAPP_INDR] <> "Y" ) VAR IsBlankAvgVacDaysNonDDA = ISBLANK(AvgVacDaysNonDDA) RETURN IF( IsBlankAvgVacDaysNonDDA || AvgVacDaysNonDDA = BLANK(), IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDaysNonDDA ), 0 ), AvgVacDaysNonDDA )Refering to Another Measure is giving blank
Hello, I have only one table with these columns. Last month column is a flag refers to the latest month in the fact table and it is generated in the sql query itself. In the dashboard I want to calculate the values of the Previous month to last month which is for example 202304 .. and I don't want to add any time slicer, I want all to happen through measures, I also don't need a date column. Any way I created a measure to get the Previous Month ID which is 202304 .. the measure is as follow Previous Month Measure = CALCULATE(SELECTEDVALUE(Fact_Sales[Month_ID]),filter(Fact_Sales, Fact_Sales[Last Month]=1))-1 Then I created another measure to calculate the sales volume based on this Previous Month ID, As follows: Volume (MT) LY = CALCULATE(SUM(Fact_Sales[SALES]), FILTER(Fact_Sales, Fact_Sales[Month_ID] = [Previous Month Measure])) The result is BLANK() However, when I add the Previous Month Definition inside the second measure as a parameter, it works perfectly. Volume (MT) LY = VAR PREVIOUS_MONTH_VARIABLE = CALCULATE(min(Fact_Sales[Month_ID])-1,filter(Fact_Sales, Fact_Sales[Last Month]=1)) Return CALCULATE(SUM(Fact_Sales[SALES]), FILTER(Fact_Sales, Fact_Sales[Month_ID] = PREVIOUS_MONTH_VARIABLE)) I need to understand why the first method is not working, also if I wanted to use the first method as splitting it to 2 measures, what should I do? Kindly find a demo in the url. https://drive.google.com/file/d/1M3y_cmFKgP2gFAiT-IQUt09n9Mt9KiEJ/view?usp=sharing ThanksAdapt a measure to show only the last value despite context
Hi, I have written this measure to get only the last date and its revenue in a table: Total Revenue by Max Date = IF( ISINSCOPE('Ohne Vouchers+ Nur Vouchers'[Last_Modified]), VAR __MAXDATE = CALCULATE( MAX('Ohne Vouchers+ Nur Vouchers'[Last_Modified]) , ALLSELECTED('Ohne Vouchers+ Nur Vouchers'[Last_Modified])) RETURN IF( MAX('Ohne Vouchers+ Nur Vouchers'[Last_Modified]) = __MAXDATE , [Total Turnover] ), [Total Turnover] ) It works well as a stand alone, but now I need to show only the last date per stage. For example: -- here the table shows the correct result: -- here the table shows the correct grand total, but should show 0 or a blank for all 3 first columns: In other words what I need is to rewrite the formula above with something else than ALLSELECTED, and then add a blank statement. I have tried but it shows zero values afterwards 😕 What is the correct function to use here instead of ALLSELECTED? Thanks!
