Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  DAX Formula Help

    Bronze Contributor
    Posted May 05, 2022 01:58 PM
    I need help with the below measure that will be used on a column graph with Month-Year as the XAxis (Ex. Jan-2021,Feb,2021,March,2021)
    1) var Maxdate1 is trying to determine the max date from my date table of any given period (Month for my visual - and my visual will only ever be at the month)
    2) HistoricalCount is trying to determine the total count all time
    3) Return is trying to display the count of all the historical data (even outside the chart month) that meets the following criteria.

    For some reason I am doing something wrong.. can anyone help?
    Measure 12 =
    var Maxdate1 = max(dim_date[DateKey])

    var HistoricalCount = CALCULATE(count(table[subscription_id]),
    filter(table,table[status] in {"Active","Paused","Closed"}),all(dim_date))

    return calculate(HistoricalCount,filter(table,table[date_key] <= Maxdate1),
    filter(table,table[cancel_date] >= Maxdate1) || table[cancel_date] = Blank()))


    ------------------------------
    Beau A
    Analyst
    MI
    ------------------------------


  • 2.  RE: DAX Formula Help

    Bronze Contributor
    Posted May 09, 2022 06:49 AM
    Hello @Beau A,
    Can you post your .pbix in order to see how is  done your tabular model please ?​

    ------------------------------
    Jean-Luc DJEKE
    Ingénieur BI
    ------------------------------



  • 3.  RE: DAX Formula Help

    Posted May 14, 2022 03:16 PM
    Edited by Vilmar Santos May 14, 2022 03:18 PM

    Tente esta abaixo. Se não der certo, envia seu pbi por favor

    Medida =
    VAR Var1 =
        MAX ( dim_date[DateKey] )
        
    VAR Var2 =
        CALCULATE (
            COUNT ( table[subscription_id] ),
            FILTER (
                table,
                table[date_key] <= Var1 &&
                table[cancel_date] >= Var1 &&
                table[status] IN { "Active", "Paused", "Closed" } ||
                table[cancel_date] = BLANK ()
            ),
            ALL ( dim_date )
        )
    RETURN
        Var2




    ------------------------------
    Vilmar Santos
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: May 05, 2022 01:57 PM
    From: Beau A
    Subject: DAX Formula Help

    Medida = VAR Var1 = MAX ( dim_date[DateKey] ) VAR VAR2 = CALCULATE ( CONTAGEM (tabela[subscription_id] ), FILTRO (tabela, mesa[date_key] <= Var1 && tabela[cancel_date] >= Var1 && tabela[status] IN { "Ativo", "Pausado", "Fechado" } || tabela[cancel_date] = BLANK () ), ALL (dim_date ) RETORNO Var2x por favor

    ------------------------------
    Vilmar Santos
    ------------------------------
    -------------------------------------------
    original Mensagem:
    Enviado: 05 de maio de 2022 01:57 PM
    De: Beau Um
    Assunto: DAX Fórmula Ajuda


    I need help with the below measure that will be used on a column graph with Month-Year as the XAxis (Ex. Jan-2021,Feb,2021,March,2021)
    1) var Maxdate1 is trying to determine the max date from my date table of any given period (Month for my visual - and my visual will only ever be at the month)
    2) HistoricalCount is trying to determine the total count all time
    3) Return is trying to display the count of all the historical data (even outside the chart month) that meets the following criteria.

    For some reason I am doing something wrong.. can anyone help?
    Measure 12 =
    var Maxdate1 = max(dim_date[DateKey])

    var HistoricalCount = CALCULATE(count(table[subscription_id]),
    filter(table,table[status] in {"Active","Paused","Closed"}),all(dim_date))

    return calculate(HistoricalCount,filter(table,table[date_key] <= Maxdate1),
    filter(table,table[cancel_date] >= Maxdate1) || table[cancel_date] = Blank()))


    ------------------------------
    Beau A
    Analyst
    MI

    ------------------------------