Forum Discussion

ACol123's avatar
ACol123
Helper I
2 years ago
Solved

Help with a clustered column chart

Hi everyone.

I'm working on a clustered column chart. I need the columns to be non-dynamic.

Let me explain better: I want when my function is = 0 or is blank to not hide the column but to return me a result equal to "-".

 

Could there be a problem with my function?

TEST =

VAR SelectedMonth = SELECTEDVALUE(PERIODSELECT[Month Period PS], MAX(PERIODSELECT[00 Month Period PS]))

VAR SelectedYear = SELECTEDVALUE(PERIODSELECT[Year Period PS], MAX(PERIODSELECT[00 Year Period PS])) RETURN CALCULATE( SUM(DB[Column1]),

DB[Column2] = "Internal",

DB[Column3] = "External",

ALL(PERIODSELECT),

DB[Year Period] = SelectedYear, DB[Month Period] <= SelectedMonth). 

 

E.g. I have a column with 4 columns and a filter on the months of the year. The four columns of the table are a,b,c,d. When I filter for February the columns "b" and "d" disappear because they have no values. I need them not to disappear and to give me back "-". How can I do it?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ACol123 ,

    Please have a try.

    TEST =
VAR SelectedMonth =
    SELECTEDVALUE (
        PERIODSELECT[Month Period PS],
        MAX ( PERIODSELECT[00 Month Period PS] )
    )
VAR SelectedYear =
    SELECTEDVALUE (
        PERIODSELECT[Year Period PS],
        MAX ( PERIODSELECT[00 Year Period PS] )
    )
VAR Result =
    CALCULATE (
        SUM ( DB[Column1] ),
        DB[Column2] = "Internal",
        DB[Column3] = "External",
        ALL ( PERIODSELECT ),
        DB[Year Period] = SelectedYear,
        DB[Month Period] <= SelectedMonth
    )
RETURN
    IF ( ISBLANK ( Result ) || Result = 0, "-", Result )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

     

3 Replies

    • ACol123's avatar
      ACol123
      Helper I
      2 years ago

      Yes, but the graph continues to hide the columns:

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ACol123 ,

    Please have a try.

    TEST =
VAR SelectedMonth =
    SELECTEDVALUE (
        PERIODSELECT[Month Period PS],
        MAX ( PERIODSELECT[00 Month Period PS] )
    )
VAR SelectedYear =
    SELECTEDVALUE (
        PERIODSELECT[Year Period PS],
        MAX ( PERIODSELECT[00 Year Period PS] )
    )
VAR Result =
    CALCULATE (
        SUM ( DB[Column1] ),
        DB[Column2] = "Internal",
        DB[Column3] = "External",
        ALL ( PERIODSELECT ),
        DB[Year Period] = SelectedYear,
        DB[Month Period] <= SelectedMonth
    )
RETURN
    IF ( ISBLANK ( Result ) || Result = 0, "-", Result )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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