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 here. Can you please help

Budget MTD = 
 If( HASONEVALUE(M_Hierarchy[L1]) ,
        SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
            "Rooms",
                2,
                3
        )
, 
        SWITCH( SELECTEDVALUE ( M_Hierarchy[L1] ),
            "Rooms",
                4,
                5
        )
)

 

 

 

 

 

 

 

Need Help
  • Majidbhatti's avatar
    Majidbhatti
    5 years ago

    Issue Fix. There was problem in relations between tables

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    In the Filter panel, highlight that visual and uncheck the Blank box for the Revenue column.  You could also do it with DAX with and AND or a nested IF, but that is easier.

    Regards,

    Pat

  • 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