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.
Hi,
I have a situation where the user wants to select a value from the slicer and the Power BI should calculate the columns based on the selected value.
Below is a clear explanation of the issue.
I have table 'A' with Threshold values that go into the slicer (1% - 100%)
Table 'B' with ID column, Network column (contains 1 or 0), and a calculated measure, Rate (in percentage format)
Now, I want to create a calculated column 'Applicable' in Table 'B' that says if the network is 1 and Rate >= selected threshold value, then 1 else, blank.
Table 'A'
Table 'B'
'Rate' measure in Table 'B' looks like below. It is calculated using a different table.
I have created a measure 'Applicable',
So whenever I select a value in the 'Threshold' slicer this measure is giving me the right result for each line in the visual but, the grand total is showing as zero (probably it is taking the average). Below is the screenshot.
I want the grand total to show the sum of the 'Applicable' measure (In this case its 3).
Is there a way to use selectedvalue in the column calculation?
I tried using the same dax to calculate a column but the selectedvalue dax is returning a null value.
Any help on this would be appreciated.
Thanks,
Vennela
Solved! Go to Solution.
Hi @Vennela ,
I'm afraid that creating calculated columns doesn't do what you want (dynamically displaying values based on slicer selection). As @amitchandak said, creating a calculated column can't get the values selected by the slicer. You can check the following blogs for the answer...
Calculated Columns and Measures in DAX
Calculated Columns vs Measures
There is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.
Best Regards
Hi @amitchandak, Thanks for your response. your solution is working if I create a measure. I can see the grand total showing the sum of the field.
But, is there a workaround to create a column instead of a measure? as I have to use this column in further calculations which would be at row level hence, it should be a calculated column.
Hi @Vennela ,
I'm afraid that creating calculated columns doesn't do what you want (dynamically displaying values based on slicer selection). As @amitchandak said, creating a calculated column can't get the values selected by the slicer. You can check the following blogs for the answer...
Calculated Columns and Measures in DAX
Calculated Columns vs Measures
There is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.
Best Regards
@Vennela , You need to create measure as column do not take slicer value
Try like
Applicable =
sumx(Table, calculate( IF(SUM('Table B'[Network])>0,IF([Rate]>=SELECTEDVALUE('Threshold'[Threshold]),1,0),0) ))
or
sumx(values(Table[ID]), calculate( IF(SUM('Table B'[Network])>0,IF([Rate]>=SELECTEDVALUE('Threshold'[Threshold]),1,0),0) ))
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 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |