Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BalaKrish
Helper I
Helper I

Calculated Measure in Calculated Column is not working

Dear All,

 

I am using Direct query in Power BI.

 

I wanted to create a slicer with custom values and change the column values in the table based on selection.

I have created tables with following values,

 

I have db table which has scores for each subjects :

 

Table Name : Score 

Column :                 Major 1     Major 2                     Major 3                                                 Scores 

                               Maths        Maths -Statistics      Maths - Statistics - Probability               100

                               Maths        Maths -Statistics      Maths - Statistics - Regression               500

                               Biology      Biology - Anatomy   Biology - Anatamy- Neurology              200

                               Biology      Biology - Anatomy   Biology - Anatamy - DigestiveSystem    800

                               Science      Science - Physics      Science - Physics - AtomicStudy            300

                               Science      Science - Physics      Science - Physics - Gravity                     1000

 

I need to display in a table visual :                             Major      Score 

                                                                                   Science    1300

 

By having a filter on Major :  

 

Table Name :    Subjects 

Column Name : Major

Values : Major 1 , Major 2 , Major 3

 

I created a calculated measure ("SelectedMajor") :   SelectedValue[Major]

 

Then i used the measure to create calculated column : 

IF(Score[SelectedMajor]="Major 1" , Score[Major1] , if(Score[SelectedMajor]="Major 2" , Score[Major2] , Score[Major3]))

 

It is not working. It is displaying only the Major 3... 

 

What may be wrong in my approach ? any suggestion? 

 

Thanks in advance 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @BalaKrish 

Create three queries in power query, then append them into one query, close &&apply,

add columns from the append query into two slicers

create a measure with DAX, then add "Major" and [Measure] inyto a table.

Capture8.JPGCapture9.JPGCapture10.JPG

query1

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major1"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major1"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major1", "Major"}})
in
    #"Renamed Columns"

query2

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major2"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major2"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major2", "Major"}})
in
    #"Renamed Columns"

query3

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major3"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major3"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major3", "Major"}})
in
    #"Renamed Columns"
Measure =
VAR s1 =
    SELECTEDVALUE ( Append1[Cate] )
VAR s2 =
    SELECTEDVALUE ( Append1[Major] )
RETURN
    SWITCH (
        s1,
        "Major1", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major1] = s2 )
        ),
        "Major2", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major2] = s2 )
        ),
        "Major3", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major3] = s2 )
        )
    )

download my file below:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/ETYbmXDBab1Lh6SnL9j...

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @BalaKrish 

Create three queries in power query, then append them into one query, close &&apply,

add columns from the append query into two slicers

create a measure with DAX, then add "Major" and [Measure] inyto a table.

Capture8.JPGCapture9.JPGCapture10.JPG

query1

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major1"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major1"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major1", "Major"}})
in
    #"Renamed Columns"

query2

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major2"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major2"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major2", "Major"}})
in
    #"Renamed Columns"

query3

let
    Source = Table.SelectColumns(#"Sheet1$",{"Major3"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Cate", each "Major3"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Major3", "Major"}})
in
    #"Renamed Columns"
Measure =
VAR s1 =
    SELECTEDVALUE ( Append1[Cate] )
VAR s2 =
    SELECTEDVALUE ( Append1[Major] )
RETURN
    SWITCH (
        s1,
        "Major1", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major1] = s2 )
        ),
        "Major2", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major2] = s2 )
        ),
        "Major3", CALCULATE (
            SUM ( 'Sheet1$'[Scores] ),
            FILTER ( 'Sheet1$', 'Sheet1$'[Major3] = s2 )
        )
    )

download my file below:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/ETYbmXDBab1Lh6SnL9j...

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-juanli-msft
Community Support
Community Support

Hi @BalaKrish 

To show dynamic columns with DAX, you could create measures like below:

https://dax.tips/2020/01/28/dynamic-columns-based-on-values/

 

Best Regards

Maggie

AntrikshSharma
Community Champion
Community Champion

Looks like it is happening because of context transition, If you have created a measure from that table and then you are using the same measure to create a calculated column, instead of that you could directly create a calculated column without a measure because a measure has hidden CALCULATE around it and because of that the currently iterated row is used to filter the measure.

Thanks for the reply Antriksh Sharma !!

 

But SELECTEDVALUE is not accepted in Calculated Column.. I need to get the filter value and it is possible only in measure so far I know. Is there any other way to do it?

 

Thanks in advance!!

Okay, so you can't take the filter from the reports/visuals and push it to the tables in the data model, a measure is filtered by filter context but in the case of a table the filter context is empty, unless CALCULATE does context transition, at this stage it is probably good to see the data model once so as to get to the nerve of the problem, can you please attach the pbix file, if the data is confidential then you can create a 10-100 row sample data.

Dear Antriksh, 

 

It is tough to show the data model as I am not supposed to.. Any suggestion to troubleshoot myself? If you can mention some pointers to check ,I will check and investigate ..

 

Thanks in advance !!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.