Forum Discussion

macintacos's avatar
macintacos
Regular Visitor
9 years ago
Solved

Measure Correct on Desktop, but Incorrect When Published

I have created a measure on Power BI Desktop to compute the number of late projects given a set of planned dates and actual dates in my dataset. The measure works correctly on Power BI Desktop and di...
  • macintacos's avatar
    macintacos
    9 years ago

    Hey thanks for responding.

     

    I deleted the measure and recreated it a few times, and suddenly the issue dissappeared...really unsure how it fixed itself, but I guess simplifying the formula (as mentioned above) and just clearing whatever cache it was working with (by deleting/recreating the measure) seems to have fixed it.

     

    I will come back to this thread if I run into this issue again.

Mi2n's avatar
Mi2n
Microsoft Employee
9 years ago

I am confused. I think it is a over complicated way to calculate something that is straight forward (or I may be wrong). Can you just use DISTINCTCOUNT() function like below?

 

Late Projects = DISTINCTCOUNT(TaskWithProjectDetail[YESProjectLate])

If this is not what you expect, can you share your sample data, and the desired result? Also, the formula used to calculate the calculated column [YESProjectLate] will also help.

  • macintacos's avatar
    macintacos
    Regular Visitor
    9 years ago

    I was unaware about the DISTINCTCOUNT() formula (thanks!), and have updated my formula:

     

    Late Projects = IF(ISBLANK(DISTINCTCOUNT(TaskWithProjectDetail[YESProjectLate])),0,DISTINCTCOUNT(TaskWithProjectDetail[YESProjectLate]))

     

    Unfortunately the sample data is sensitive in nature and I cannot share it. The column YESProjectLate returns the project name if the 'Planned Finish Date' (one date field) is behind the 'Actual Finish Date' (another date field), and returns blank if the project is on time. I needed to count distinct values in the YESProjectLate column because there are many rows with the same project name (this is how the data is strictured, so take it as a given that I cannot change).

     

    For example:

     

    Project NamePlanned Finish DateActual Finish DateYESProjectLate
    ProjectA8/16/20169/16/2016ProjectA
    ProjectB8/16/20167/16/2016 
    ProjectC8/16/20168/20/2016ProjectC

     

    However I'm still running into the same issue. Power BI Desktop returns the correct value, and the published version does not. 

     

    • ElliotP's avatar
      ElliotP
      Post Prodigy
      9 years ago

      The published version from memory uploads your pbix, your data source conditions and details to powerbi web.

       

      My biggest thing would be about the date table. I couldn't see if you have one or not, my suggestion would be to download a date table from the azure marketplace (free), through powerbi, "ExtendedCalender", draw a relationship link between your data table and the date table and the use the datekey in the date table as your date value instead of the date value in your data table.

    • Mi2n's avatar
      Mi2n
      Microsoft Employee
      9 years ago

      Can you give the exact formula used to calculate the YESProjectLate column?

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      macintacos

       

      What's your data source?

       

      I haven't reproduced your issue.

       

      I created a sample table like yours, the measure shows same data in Power BI Desktop and Service.

       

      Late Projects =
IF (
    ISBLANK (
        COUNTROWS (
            FILTER (
                DISTINCT ( SELECTCOLUMNS ( Table3, "Late", Table3[YESProjectLate] ) ),
                NOT ( ISBLANK ( [Late] ) )
            )
        )
    ),
    0,
    COUNTROWS (
        FILTER (
            DISTINCT ( SELECTCOLUMNS ( Table3, "Late", Table3[YESProjectLate] ) ),
            NOT ( ISBLANK ( [Late] ) )
        )
    )
)

       

       

       

       

       

      Regards,

      • macintacos's avatar
        macintacos
        Regular Visitor
        9 years ago

        Hey thanks for responding.

         

        I deleted the measure and recreated it a few times, and suddenly the issue dissappeared...really unsure how it fixed itself, but I guess simplifying the formula (as mentioned above) and just clearing whatever cache it was working with (by deleting/recreating the measure) seems to have fixed it.

         

        I will come back to this thread if I run into this issue again.