Measure Correct on Desktop, but Incorrect When Published

[macintacos]

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 displays the correct number. However, when I publish it to a Power BI Dashboard online, the number is incorrect. I have tried various forms of deleting/re-publishing the measure, but they have not worked thus far. I don't want to change my measure calculation because I know that it is correct. Any ideas why this could be happening?

The measure counts the number of projects that have a calculated column [YESProjectLate] (this is the reasoning for the filtering in the middle of the equations). Again, the number is correct on Desktop, but incorrect online, and as such I really would like to keep this the same:

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

[Anonymous]

Hi 

I just remove the data set and publish it again it is working. not sure how need some more update if possible.

THanks  

[Mi2n]

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]

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 Name	Planned Finish Date	Actual Finish Date	YESProjectLate
ProjectA	8/16/2016	9/16/2016	ProjectA
ProjectB	8/16/2016	7/16/2016
ProjectC	8/16/2016	8/20/2016	ProjectC

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

[v-sihou-msft]

@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 · Accepted solution]

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.

[lovedatsnow]

I ran into this same issue. Was able to resolve by unpublishing the app, then republishing it. Looks like that may have reset from cache in the power bi service.

[Mi2n]

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

[ElliotP]

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.