Measure with variables , can not acces a column from a virtual table

DAX
1

Hello,

I’m struggling with a measure using Variables to create virtual tables. I can not access
a column from a Var table
first screenshot is the measure

image
image945×876 182 KB

second screenshot is the virtual table
image
image945×830 178 KB

when I create the table the simple measure works but when I use the same table in a measure it fails to give a result.
any help is greatly appreciated

Roger
where to find a video " how to work with virtual tables and their columns " ?

2

Hi @Roger

Did you searching the EDNA platform? Youtube? Did you try using DataMentor/EDNA AI Tools that are built within the EDNA Platform?

You can put the code in DataMentor using to review your code and figure out what the coding is using debugger option along with other options.

I typed in virtual tables in Youtube many videos come up

Brian did a video on youtube several years ago that might help you.
debugging virtual tables

Tryout DataMentor that will help you with your problem.

Thanks
Keith

3

@Roger, CALCULATE can’t directly reference columns from virtual tables. You need to materialize the virtual table and then iterate over it to perform the calculation.

Try this revision:

TONNAGE TOTAL INDUSTRIE FORM VIRTUAL =
VAR CurrentYear =
    SUMMARIZE(
        MAANDRAPPORTEN,
        MAANDRAPPORTEN[VOEDSELBANK],
        MAANDRAPPORTEN[Periode],
        MAANDRAPPORTEN[PROD ID],
        MAANDRAPPORTEN[HEADING],
        "HOEVELEID IN KG", SUM(MAANDRAPPORTEN[KG NA RAMASSE])
    )
VAR MergedTables =
    SUMMARIZE(
        FILTER(
            ADDCOLUMNS(
                UNION(CurrentYear, 'HISTORIEK'),
                "IN or OUT", LOOKUPVALUE(SOURCES[VOORRAAD], SOURCES[HEADING], [HEADING]),
                "KG VB", [HOEVELEID IN KG]
            ),
            [IN or OUT] = "IN"
        ),
        [Periode],
        [VOEDSELBANK],
        [heading],
        [IN or OUT],
        [KG VB]
    )
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                MergedTables,
                MergedTables[HEADING] = "DISTRIBUTION DISTRIBUTIE"
            ),
            [KG VB]
        )
    )
4

Hello,
I found the solution myself after some debugging the code

for whom is interested below the solution for the measure
calculating the sum from a column in a virtual table.
solution is to wrap the sumx() in a calculate to make it work.
the KG column is also in the combinedData Table
a tip for the one struggling with the variables
I found the video by Brian Julius very well explained
below the link
Top 5 Tips & Tricks For Debugging Virtual Tables In Power BI (youtube.com)

image
image597×658 27.9 KB

kind regards

Roger

5

thats the video i saw and suggested

6

@ David
thanks for your suggestion the sumx() solved the problem

@ keith
maybe a video on this topic would be helpfull, I see many readers
struggling with this topic
kind regards
Roger

7

HI @Roger

Did you try using DataMentor/EDNA AI tool that is built within the Edna platform? Give it try as it has great information to help you.

Great idea.

@EnterpriseDNA team will have to do that or suggest another way.

thanks
Keith