Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

/ 30 (days)

Hi All,    This is my current formula (see the top formula below), but it does not work correctly because some days there is no activity so I need to tell PowerBI to include the empty cells as 0. ...
DAX
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Add another ) after 0 ) so you will have there 0 )),

  • Anonymous's avatar
    Anonymous
    4 years ago
    Average Durations =
    CALCULATE (
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
           COALESCE ( [SDR ID average per CP ID], 0 )),
    'FACT TABLE'[Charger Type] = 'Active'
     
    That does look good, the problem is that I am getting an error: 'Cannot find table 'Active'. 
    I do not have a table with Active/Non Active column. 
    Is there a different way of doing this. My line graph is showing "0" according to the calendar before the item started operating and runs into the future months (I guess as per the calendar). 
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Well, that is because you replace blank with 0 in your original measure. 

    So forget what we did, it's not related to your issue.

    Why did you replace the blank with 0 inside the coalesce? You have blanks inside the range?

    Anyway, if you share the file and what you need I could solve this quickly. Will need to continue later, going away from PC, so will ping here later tonight. Until then please try to share the file or a sample of it.

    P.S. appreciate your kudos

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous wrap it with calculate like this:

CALCULATE(

[Your function],

TableName[Charger] = 'Active'

)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I'm not that profficient in Dax: 

    SDR ID average per CP ID avgerage by Date2 =
    CALCULATE(
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
           COALESCE ( [SDR ID average per CP ID], 0 ),
    [FACT TABLE(Charger Type]) = 'Active'
    )
    Please rearrange for me. This is obviously wrong. Thank you so much
    • SpartaBI's avatar
      SpartaBI
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymousthe code is correct, just remove the [ before the FACT.

      If you don't get the right result then the question does the original measure works fine for all charger types?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago
        Average Durations =
        CALCULATE(
        AVERAGEX (
            VALUES ( 'Calendar'[Date] ),
               COALESCE ( [SDR ID average per CP ID], 0 ),
        FACT TABLE[Charger Type] = 'Active',
        )
         
        I am getting an error: "Too many arguments for AVERAGEX, max is 2