Forum Discussion

Zakaria_1980's avatar
Zakaria_1980
Icon for Post Patron rankPost Patron
4 years ago
Solved

Get blank() as output

Hi,   I've DB with Target for each module and how many achieved per City. so when I want to get the gap for each Module and per city, if in the DB in one of the cities, one of the modules was not f...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Zakaria_1980 ,

     

    It looks like a matrix. But you chose a card visual, which is puzzling.

     

    You can use the matrix visual to show the same effect.

     

     

     

    Here's the solution.

    1.Does your data model only have one table?

    If it is, use dax to create a Module table.

    Table 2 = DISTINCT('Table'[Module])

     

    2.Relationship is as follows.

     

    3.Create two measures.

    Target value = 
var _sum=SUM('Table'[Target])
return
IF(ISBLANK(_sum),"N/A",_sum)
    Acieved value = 
var _sum=SUM('Table'[Achieved])
return
IF(ISBLANK(_sum),"N/A",_sum)

     

    Then we get the results.

     

    Check the attachment for more details.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
4 years ago

HI Zakaria_1980 ,

 

Sorry I didn't understand the calculation that is giving you BLANK() here.

Can you please paste your formula for the calculation here please?

We just need to modify the calculation somehow to replace BLANK() with N/A.

 

Thanks,

Pragati

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    Hi Zakaria_1980 ,

     

    If you want to avoid BLANK(), then you will have to create a measure to handle this.

    I am assuming:

    • you are moving SUM(Consolidated[Certification Applcable]) to your card visual with an additional filter on Module = "PMO Practicioner".
    • Both Certification Applcable and Module columns are in same table Consolidated

     

    Write a measure something as below:

    cardCalc = 
var calc = CALCULATE(
                     SUM(Consolidated[Certification Applcable]), 
                     Consolidated[Module] = "PMO Practicioner"
                    )
RETURN
SWITCH(
       TRUE(), 
       calc = BLANK(), "N/A", calc
      )

    Just check the DAX once specially for any naming conventions and filter expression values.

    Move this measure to your card visual. It should display "N/A" not BLANK() now.

     

    Thanks,

    Pragati