Can't load data for the visual - text and integers

Anonymous · ‎12-17-2019

Hi there,

I'm getting the 'can't load the data for this visual error' with mention of DAX not being about to compare text and integers. I can't find any text in the columns the formula is referecing, so not sure what's going on!

Is anyone able to assist?

Below is my formula... I'm trying to find previous week figures

Post Volume Last Week = SUMX(

FILTER(ALL('DateKey'),

IF(SELECTEDVALUE('DateKey'[Week #])=1,

'DateKey'[Week #]=CALCULATE(MAX('DateKey'[Week #]), ALL('DateKey')) && 'DateKey'[Year]=FORMAT(VALUE(SELECTEDVALUE('DateKey'[Year]))-1,""),

'DateKey'[Week #]=SELECTEDVALUE('DateKey'[Week #])-1 && 'DateKey'[Year]=FORMAT(VALUE(SELECTEDVALUE('DateKey'[Year])),""))

),

[Sum Post Volume])

Thank you!

Sonia

Icey · ‎12-18-2019

Hi @Anonymous ,

In your DAX expression, the result format of below expression is "Text". It is caused by FORMAT, which converts a value to text according to the specified format.

FORMAT(VALUE(SELECTEDVALUE('DateKey'[Year]))-1,"")

no format.PNG with format.PNG

Why not try to write your formula without using FORMAT function?

Post Volume Last Week 2 =
SUMX (
    FILTER (
        ALL ( 'DateKey' ),
        IF (
            SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
            'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
                && 'DateKey'[Year]
                    = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1,
            'DateKey'[Week #]
                = SELECTEDVALUE ( 'DateKey'[Week #] ) - 1
                && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
        )
    ),
    [Sum Post Volume]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎12-18-2019

Thank you, Icey!

It's no longer showing the same error, but is now showing 'Blank'....

Any ideas??

Thanks again,

Sonia

Icey · ‎12-18-2019

Hi @Anonymous ,

What about this?

Post Volume Last Week 2 =
IF (
    SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
    SUMX (
        FILTER (
            ALL ( 'DateKey' ),
            'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
                && 'DateKey'[Year]
                    = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
        ),
        [Sum Post Volume]
    ),
    SUMX (
        FILTER (
            ALL ( 'DateKey' ),
            'DateKey'[Week #]
                = SELECTEDVALUE ( 'DateKey'[Week #] ) - 1
                && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
        ),
        [Sum Post Volume]
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎12-19-2019

Hey @Icey

Sorry, still coming through as 'Blank'!

My Datekey table is all linked up to my Post Data table (where I'm trying to find the sum of post volume, and other things) , but is it something to do with the fact the max date on my datekey table is in 2020?

Thanks again for your help

Sonia

Icey · ‎12-19-2019

Hi @Anonymous ,

It works well in my PBIX file. Please check the attached PBIX file. If it is different from your data structure, please modify it.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎12-19-2019

Hi @Icey

I think my data is a little different to yours, but should still be structured correctly (i hope!)

I've stripped out any sensitive info, but below is the 'post data' that I'm working with, that is linked to my 'datekey' table. I'm trying to find things like sum of post volume, average engagement rate etc. , looking at week-on-week (ie: sum of post volume over past 7 days, and the 7 days prior to that etc.)

POST DATA

DATEKEY TABLE (LINKED TO POST DATA)

Does this help at all?

Thank you again,

Sonia

Icey · ‎12-19-2019

Hi @Anonymous ,

To help you further, please share me a dummy PBIX file with the two tables and all measures you created with them. Please remove sensitive information.

Best Regards,

Icey

Anonymous · ‎12-21-2019

Hi @Icey

Ok, will do!

Whats the best way to share with you? I noticed there's no 'attach file' option here...

Icey · ‎12-22-2019

Hi @Anonymous ,

I suggest you to share your PBIX file with OneDrive for Business. Attach the link. Please delete sensitive information.

Best Regards,

Icey

Anonymous · ‎01-01-2020

Hi @Icey ,

So sorry for the delay!

See below link to the dummy power bi file - I hope this works? I've just included some basic information:

Power BI file:

https://www.dropbox.com/s/ytlm7np0su2eane/Example%20file.pbix?dl=0

And here is the excel file:

https://www.dropbox.com/s/u0l905u2uvk7ob4/data%20for%20power%20bi.xlsx?dl=0

The measures I'd like to create:

- Average engagement rate for last week

- Average engagement rate for the week prior to last week

- Week on week change

- Average weekly engagement rate YTD

- % change last week vs YTD average

I also love to be able to graph last week, week before last, and YTD ave all on the one graph!

Like the below, but with YTD also..

Thank you SO SO much for your help!

Let me know if any questions

Sonia

Icey · ‎01-03-2020

Hi @Anonymous ,

Please check if this is what you want:

Average engagement rate for last week =
IF (
    SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
    AVERAGEX (
        FILTER (
            ALL ( 'DateKey' ),
            'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
                && 'DateKey'[Year]
                    = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
        ),
        [Ave Eng Rate]
    ),
    AVERAGEX (
        FILTER (
            ALL ( 'DateKey' ),
            'DateKey'[Week #]
                = SELECTEDVALUE ( 'DateKey'[Week #] ) - 1
                && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
        ),
        [Ave Eng Rate]
    )
)

Average engagement rate for the week prior to last week =
IF (
    SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
    AVERAGEX (
        FILTER (
            ALL ( 'DateKey' ),
            'DateKey'[Week #]
                = CALCULATE ( MAX ( 'DateKey'[Week #] ) - 1, ALL ( 'DateKey' ) )
                && 'DateKey'[Year]
                    = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
        ),
        [Ave Eng Rate]
    ),
    IF (
        SELECTEDVALUE ( 'DateKey'[Week #] ) = 2,
        AVERAGEX (
            FILTER (
                ALL ( 'DateKey' ),
                'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
                    && 'DateKey'[Year]
                        = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
            ),
            [Ave Eng Rate]
        ),
        AVERAGEX (
            FILTER (
                ALL ( 'DateKey' ),
                'DateKey'[Week #]
                    = SELECTEDVALUE ( 'DateKey'[Week #] ) - 2
                    && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
            ),
            [Ave Eng Rate]
        )
    )
)

Average weekly engagement rate YTD = TOTALYTD([Ave Eng Rate],DateKey[Date])

Week on week change = [Average engagement rate for last week]-[Average engagement rate for the week prior to last week]

% change last week vs YTD average = [Week on week change]/[Average weekly engagement rate YTD]

To create the chart, enter a Measure Name table first.

Then, create a measure.

Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Measure Name'[Measure Name] ) = "Average engagement rate for last week", 
    [Average engagement rate for last week],
    SELECTEDVALUE ( 'Measure Name'[Measure Name] ) = "Average engagement rate for the week prior to last week", 
    [Average engagement rate for the week prior to last week],
    SELECTEDVALUE ( 'Measure Name'[Measure Name] ) = "Week on week change", 
    [Week on week change],
    SELECTEDVALUE ( 'Measure Name'[Measure Name] ) = "Average weekly engagement rate YTD", 
    [Average weekly engagement rate YTD],
    SELECTEDVALUE ( 'Measure Name'[Measure Name] ) = "% change last week vs YTD average", 
    [% change last week vs YTD average]
)

In the last, create a chart.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎01-03-2020

Hi @Icey ,

Thank you so much for this.

Unfortunately, the measures below still aren't working for me! I'm getting 'blank' values.

Below is the file again with the measures added (so you can see the blank issue)

https://www.dropbox.com/s/85v0wkkw4ez7w9d/Example%20file.pbix?dl=0

One other measure I'm having trouble with is: 'average total weekly engagement YTD' (using the total engagement measure, rather than eng rate)

(And apologies for the confusion - I don't need to display all the new measures on the one graph. One i have 'last week', 'week prior to last' and 'ytd average', i'll just bring them all in as 'values' on the graph, as per the below:

Thanks again!

Sonia

Icey · ‎01-07-2020

Hi @Anonymous ,

First, the measures show blank are caused by "Nothing selected". If you select year and week, it will show values.

Or, you can set the value shows when nothing is selected. For example,

Average engagement rate for last week = 
IF (
    SELECTEDVALUE ( DateKey[Week #] ) = BLANK ()
        || SELECTEDVALUE ( DateKey[Year] ) = BLANK (),
    CALCULATE (
        [Ave Eng Rate],
        FILTER (
            ALL ( DateKey ),
            DateKey[Date]
                <= MAXX (
                    FILTER (
                        ALL ( DATA ),
                        DATA[WEEK NUM]
                            = WEEKNUM ( MAXX ( ALL ( DATA ), DATA[Published Date] ) ) - 1
                    ),
                    DATA[Published Date]
                )
        )
    ),
    IF (
        SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,
        AVERAGEX (
            FILTER (
                ALL ( 'DateKey' ),
                'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )
                    && 'DateKey'[Year]
                        = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1
            ),
            [Ave Eng Rate]
        ),
        AVERAGEX (
            FILTER (
                ALL ( 'DateKey' ),
                'DateKey'[Week #]
                    = SELECTEDVALUE ( 'DateKey'[Week #] ) - 1
                    && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )
            ),
            [Ave Eng Rate]
        )
    )
)

Other measures please check the attached PBIX file.

Then, for the column chart, enter a table.

Create a measure.

Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Legend[Legend] ) = "FYTD Average", [Average weekly engagement rate YTD],
    SELECTEDVALUE ( Legend[Legend] ) = "Week prior to last", [Average engagement rate for the week prior to last week],
    SELECTEDVALUE ( Legend[Legend] ) = "Last week", [Average engagement rate for last week]
)