Forum Discussion

KasperJ90's avatar
KasperJ90
Icon for Helper III rankHelper III
3 years ago

Run a query against a dataset in Power Query measure calculation in row context

Hi,

 

I succesfully set up a flow for a table in my Power BI dataset. I am loading this created CSV file into my dataset as a way of saving historical data:

 

Now I want to do this for another table, but this tables includes measures. I need the result of the measure to be calculated for each row in the data I save similar to if it was a column.
This means the Total Sales Measure should only be calculated for each row. I do not understand how the calculation is being performed now, but the numbers I receive are way to high but still different from row to row in the CSV file I create.
See below a part of the code in the "run a query against a dataset" section in Power Automate. 

DEFINE
VAR _Table =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Year] > 2020
)
,"Customer", 'Table'[Customer]  ''<<< COLUMN
,"Product", 'Table'[Product]  ''<<< COLUMN
,"Total Sales", '[Total Sales Measure]  ''<<< MEASURE
)
EVALUATE
_Table

Current result example:  
CustomerProductTotal Sales Measure
AX67.000.000
BY43.000.000
   
Requested Result example:  
CustomerProductTotal Sales Measure
AX3.500
BY1.500

 

I can not add the Total Sales measure as a calculated column etc. but need the result for each line so I can compare changes over time. 
Can you help explaining how the measures are being calculated and more importantly how to get the correct results?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi KasperJ90 ，

    Please try:

    DEFINE
VAR _Table =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Year] > 2020
),
"Customer", 'Table'[Customer],
"Product", 'Table'[Product],
"Total Sales", CALCULATE('[Total Sales Measure]', ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Product]))
)
EVALUATE
_Table

    The function removes all filters from the 'Table' except for the filters on 'Table'[Customer] and 'Table'[Product], ensuring that the measure is calculated for each row considering the specific Customer and Product. 

    Please try this modified DAX query and see if it provides the correct results for your scenario.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum