Forum Discussion

Gintare_D's avatar
Gintare_D
Copper Contributor
May 21, 2022

DAX: conditional measure in Power Pivot column

Dears,    I am wondering if there are expects that would be able to solve this, at a first glance simple, task.   I need Power Pivot with Type of discount and actual %, seems simple. Drop an eye ...
BI & Data Analysis
power bi
flexyourdata's avatar
flexyourdata
Iron Contributor
May 22, 2022

Gintare_D 

 

If I have understood correctly, you are not concerned about how to create the calculation, but how to integrate the parameter, which should be selected or entered somewhere on a sheet in the workbook. 

 

If that's the case, then you can create a named cell which contains the parameter:

 

Here, I have named B2 "discount_type":

 

 

After this, I can create a new query by using Data>Get & Transform Data>Get Data>From other Sources>Blank Query:

 

 

You can put this code in the Advanced Editor:

let
    Source = Excel.CurrentWorkbook(){[Name="discount_type"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "discount_type"}})
in
    #"Renamed Columns"

 

This will give you a query you can then use Home>Close & Load to>(Only create connection + Add this data to the data model)

 

 

Now you will have a new table in the Data Model with a single cell. You can refer to this discount type in your formula to calculate the discounted amount or whatever you need to do. 

 

Without more details about your existing data, it is difficult to offer more advice on the exact DAX to use in the measure or calculated column.

Resources