Forum Discussion

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
5 years ago
Solved

HASONEVALUE adding BLANK row

I am trying to fix TOTALS of a Matrix but ending up with adding a BLANK row as shown in the image below   My measure is as below, I have values to replace 2, 3, 4, & 5 but keeping it simple h...
Need Help
  • Majidbhatti's avatar
    Majidbhatti
    5 years ago

    Issue Fix. There was problem in relations between tables

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Majidbhatti , Try a measure like

Budget MTD = sumx(values(M_Hierarchy[revenue]), // Use the correct table
If( HASONEVALUE(M_Hierarchy[L1]) ,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
2,
3
)
,
SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
"Rooms",
4,
5
)
))

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
5 years ago

mahoneypat , i wish it was that easy but BLANK is not showing in the visual filter

amitchandak , All items are SUMXed which is blowing out the totals. 

See the problem below

 

 

Budget MTD (Rev) = 
Var vMonthDays = DATEDIFF( STARTOFMONTH(DateTable[Date]) , ENDOFMONTH(DateTable[Date])+1 , DAY )
Var vVal = CALCULATE(
                TOTALMTD([Actual Amount (AX)], DateTable[Date],AX_Data[Type] = "B") ,
                M_Hierarchy[L1Code] <> "9"
            ) 

Return

If( HASONEVALUE(M_Hierarchy[L1]) ,
    SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
        "Rooms",
            vVal,
        (vVal / vMonthDays ) * SELECTEDVALUE(DateTable[DAY])
        )
        ,
          //THIS SECTION IS THE PROBLEM  
          CALCULATE(
                TOTALMTD([Actual Amount (AX)], DateTable[Date],AX_Data[Type] = "B") ,
                M_Hierarchy[L1Code] <> "9"
            )
)

 

  • Majidbhatti's avatar
    Majidbhatti
    Icon for Advocate IV rankAdvocate IV
    5 years ago

    Issue Fix. There was problem in relations between tables