Forum Discussion

EugenioProlog's avatar
EugenioProlog
Helper III
1 year ago
Solved

How do I Hide columns based on metric?

I have this Cohort on a matrix table that has Columns that vary according to a column on one of my tables. I want it to display only values that are not null. Is there a way to do that?

 


I'm trying something like this: 
IsLifetimeValid =
VAR CurrentLifetime = SELECTEDVALUE(Lifetime[Lifetime])
RETURN
IF(
NOT(ISBLANK(CurrentLifetime)) && CurrentLifetime <= [Maximo Lifetime],
TRUE,
FALSE
)

But it returns true to all the values, not just the ones that are smaller or equal to 17.

 

Here is the attachment to my pbix file.
https://drive.google.com/file/d/1frnBF0q_iSchNtfZoQGGpthfLihic9gy/view?usp=sharing

Need Help
  • Demert's avatar
    Demert
    1 year ago

    Hi EugenioProlog ,

     

    You can simply filter out the blanks in your filter pane by doing this:

    This will not work for Cohort % because you always return 0 in the measure.

     

    To fix this i suggest the following code 

    Cohort % = 
VAR Numerador = 
    CALCULATE(
        [Subscriptions], 
        FILTER(
            client_base,
            client_base[lifetime_months] >= SELECTEDVALUE(Lifetime[Lifetime])
        )
    )
VAR Denominador = [Subscriptions]

RETURN 
if (ISBLANK(Numerador),BLANK(),
   0+ DIVIDE(Numerador, Denominador, 0) ) // Retorna 0 se o denominador for 0)

    And then also filter out the blanks for this measure out in your filter pane.

2 Replies

  • Demert's avatar
    Demert
    Resolver III
    1 year ago

    Hi EugenioProlog ,

     

    You can simply filter out the blanks in your filter pane by doing this:

    This will not work for Cohort % because you always return 0 in the measure.

     

    To fix this i suggest the following code 

    Cohort % = 
VAR Numerador = 
    CALCULATE(
        [Subscriptions], 
        FILTER(
            client_base,
            client_base[lifetime_months] >= SELECTEDVALUE(Lifetime[Lifetime])
        )
    )
VAR Denominador = [Subscriptions]

RETURN 
if (ISBLANK(Numerador),BLANK(),
   0+ DIVIDE(Numerador, Denominador, 0) ) // Retorna 0 se o denominador for 0)

    And then also filter out the blanks for this measure out in your filter pane.

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi EugenioProlog ,

     

    You issue is the fact that you are using a COALESCE on your metric:

    This forces the value within the if to return 0 not at the metric itself but that the aggregated level of the calculation

    I assume you want to have the information only at the month year level try to add a is inscope to your calculation:

     

    See file attach.

    Cohort = 
VAR MaxLifetime = 
    CALCULATE(
        MAX(client_base[lifetime_months]),
        ALLEXCEPT(client_base, dim__calendar[dsc_mesano])
    )
RETURN
    IF(
        SELECTEDVALUE(Lifetime[Lifetime]) <= MaxLifetime && ISINSCOPE(dim__calendar[dsc_mesano]),
        COALESCE(
            CALCULATE(
                [Subscriptions],
                FILTER(
                    client_base,
                    client_base[lifetime_months] >= SELECTEDVALUE(Lifetime[Lifetime])
                )
            ),
            0
        )
    )