Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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:
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.
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.
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:
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.
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
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 !!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |