Forum Discussion

RitaHL's avatar
RitaHL
Frequent Visitor
2 years ago
Solved

Show empty category in a table visual

Good morning, 

 

I was trying to get a 

 

Total Number of order = DISTINCTCOUNT('Table 1'[Column A])+sum('Table 2'[column B])
 
In Table 1, I put a record of the category I want but without value in column A. When I drop the measure in the visual, that category shows up but the measure shows a count of 1. Is there a way to not having this count but keep the category? 
 
Many thanks in adavance!
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, RitaHL 

    Based on your information, I create sample tables:

     

    Then create a new measure and try the following DAX:

    Total Number of Order = 
VAR _Total =  CALCULATE(
            DISTINCTCOUNT('Table'[Column A]),
            'Table'[Column A] <> BLANK()
        ) + 
        SUMX(
            FILTER('Table 2', NOT(ISBLANK('Table 2'[Column B]))),
            'Table 2'[Column B]
        )
RETURN
IF(
    ISBLANK(_Total),
    "No Value",
    _Total
    )

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, RitaHL 

    Based on your information, I create sample tables:

     

    Then create a new measure and try the following DAX:

    Total Number of Order = 
VAR _Total =  CALCULATE(
            DISTINCTCOUNT('Table'[Column A]),
            'Table'[Column A] <> BLANK()
        ) + 
        SUMX(
            FILTER('Table 2', NOT(ISBLANK('Table 2'[Column B]))),
            'Table 2'[Column B]
        )
RETURN
IF(
    ISBLANK(_Total),
    "No Value",
    _Total
    )

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

    • RitaHL's avatar
      RitaHL
      Frequent Visitor
      2 years ago

      On the category item, this is selected. 

       

      On the measure itself, this option does not exist. 

      I was expecting to see this category shows up, and nothing or 0 in the column of the measure. However, the category shows up and the measure shows 1.